<!DOCTYPE html>


<html lang="zh-CN">


<head>
  <meta charset="utf-8" />
    
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1" />
  <title>
     
  </title>
  <meta name="generator" content="hexo-theme-ayer">
  
  <link rel="shortcut icon" href="/favicon.ico" />
  
  
<link rel="stylesheet" href="/dist/main.css">

  
<link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/Shen-Yu/cdn/css/remixicon.min.css">

  
<link rel="stylesheet" href="/css/custom.css">

  
  
<script src="https://cdn.jsdelivr.net/npm/pace-js@1.0.2/pace.min.js"></script>

  
  

  

</head>

</html>

<body>
  <div id="app">
    
      
    <main class="content on">
      
<section class="cover">
    
      
      <a class="forkMe" href="https://github.com/Shen-Yu/hexo-theme-ayer"
        target="_blank"><img width="149" height="149" src="/images/forkme.png"
          class="attachment-full size-full" alt="Fork me on GitHub" data-recalc-dims="1"></a>
    
  <div class="cover-frame">
    <div class="bg-box">
      <img src="/images/cover1.jpg" alt="image frame" />
    </div>
    <div class="cover-inner text-center text-white">
      <h1><a href="/">Hexo</a></h1>
      <div id="subtitle-box">
        
        <span id="subtitle"></span>
        
      </div>
      <div>
        
      </div>
    </div>
  </div>
  <div class="cover-learn-more">
    <a href="javascript:void(0)" class="anchor"><i class="ri-arrow-down-line"></i></a>
  </div>
</section>



<script src="https://cdn.jsdelivr.net/npm/typed.js@2.0.11/lib/typed.min.js"></script>


<!-- Subtitle -->

  <script>
    try {
      var typed = new Typed("#subtitle", {
        strings: ['面朝大海，春暖花开', '何来天才，唯有苦练', '集中一点，登峰造极'],
        startDelay: 0,
        typeSpeed: 200,
        loop: true,
        backSpeed: 100,
        showCursor: true
      });
    } catch (err) {
      console.log(err)
    }
  </script>
  
<div id="main">
  <section class="outer">
  
  

<div class="notice" style="margin-top:50px">
    <i class="ri-heart-fill"></i>
    <div class="notice-content" id="broad"></div>
</div>
<script type="text/javascript">
    fetch('https://v1.hitokoto.cn')
        .then(response => response.json())
        .then(data => {
            document.getElementById("broad").innerHTML = data.hitokoto;
        })
        .catch(console.error)
</script>

<style>
    .notice {
        padding: 20px;
        border: 1px dashed #e6e6e6;
        color: #969696;
        position: relative;
        display: inline-block;
        width: 100%;
        background: #fbfbfb50;
        border-radius: 10px;
    }

    .notice i {
        float: left;
        color: #999;
        font-size: 16px;
        padding-right: 10px;
        vertical-align: middle;
        margin-top: -2px;
    }

    .notice-content {
        display: initial;
        vertical-align: middle;
    }
</style>
  
  <article class="articles">
    
    
    
    
    <article
  id="post-mysql/01.MySQL之mysqldump的使用"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h2 itemprop="name">
  <a class="article-title" href="/2020/11/11/mysql/01.MySQL%E4%B9%8Bmysqldump%E7%9A%84%E4%BD%BF%E7%94%A8/"
    >01.MySQL之mysqldump的使用.md</a> 
</h2>
 

    </header>
     
    <div class="article-meta">
      <a href="/2020/11/11/mysql/01.MySQL%E4%B9%8Bmysqldump%E7%9A%84%E4%BD%BF%E7%94%A8/" class="article-date">
  <time datetime="2020-11-10T16:00:00.000Z" itemprop="datePublished">2020-11-11</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/mysql/">mysql</a>
  </div>
   
    </div>
      
    <div class="article-entry" itemprop="articleBody">
       
  <h1 id="MySQL之mysqldump的使用"><a href="#MySQL之mysqldump的使用" class="headerlink" title="MySQL之mysqldump的使用"></a>MySQL之mysqldump的使用</h1><h2 id="一、mysqldump-简介"><a href="#一、mysqldump-简介" class="headerlink" title="一、mysqldump 简介"></a>一、mysqldump 简介</h2><p><code>mysqldump</code> 是 <code>MySQL</code> 自带的逻辑备份工具。</p>
<p>它的备份原理是通过协议连接到 <code>MySQL</code> 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的<code>insert</code> 语句，当我们需要还原这些数据时，只要执行这些 <code>insert</code> 语句，即可将对应的数据还原。</p>
<h2 id="二、备份命令"><a href="#二、备份命令" class="headerlink" title="二、备份命令"></a>二、备份命令</h2><h3 id="2-1-命令格式"><a href="#2-1-命令格式" class="headerlink" title="2.1 命令格式"></a>2.1 命令格式</h3><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqldump [选项] 数据库名 [表名] &gt; 脚本名</span><br></pre></td></tr></table></figure>

<p>或</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqldump [选项] --数据库名 [选项 表名] &gt; 脚本名</span><br></pre></td></tr></table></figure>

<p>或</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqldump [选项] --all-databases [选项]  &gt; 脚本名</span><br></pre></td></tr></table></figure>

<h3 id="2-2-选项说明"><a href="#2-2-选项说明" class="headerlink" title="2.2 选项说明"></a>2.2 选项说明</h3><table>
<thead>
<tr>
<th>参数名</th>
<th>缩写</th>
<th>含义</th>
</tr>
</thead>
<tbody><tr>
<td>–host</td>
<td>-h</td>
<td>服务器IP地址</td>
</tr>
<tr>
<td>–port</td>
<td>-P</td>
<td>服务器端口号</td>
</tr>
<tr>
<td>–user</td>
<td>-u</td>
<td>MySQL 用户名</td>
</tr>
<tr>
<td>–pasword</td>
<td>-p</td>
<td>MySQL 密码</td>
</tr>
<tr>
<td>–databases</td>
<td></td>
<td>指定要备份的数据库</td>
</tr>
<tr>
<td>–all-databases</td>
<td></td>
<td>备份mysql服务器上的所有数据库</td>
</tr>
<tr>
<td>–compact</td>
<td></td>
<td>压缩模式，产生更少的输出</td>
</tr>
<tr>
<td>–comments</td>
<td></td>
<td>添加注释信息</td>
</tr>
<tr>
<td>–complete-insert</td>
<td></td>
<td>输出完成的插入语句</td>
</tr>
<tr>
<td>–lock-tables</td>
<td></td>
<td>备份前，锁定所有数据库表</td>
</tr>
<tr>
<td>–no-create-db/–no-create-info</td>
<td></td>
<td>禁止生成创建数据库语句</td>
</tr>
<tr>
<td>–force</td>
<td></td>
<td>当出现错误时仍然继续备份操作</td>
</tr>
<tr>
<td>–default-character-set</td>
<td></td>
<td>指定默认字符集</td>
</tr>
<tr>
<td>–add-locks</td>
<td></td>
<td>备份数据库表时锁定数据库表</td>
</tr>
</tbody></table>
<h3 id="2-3-实例"><a href="#2-3-实例" class="headerlink" title="2.3 实例"></a>2.3 实例</h3><p>备份所有数据库：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqldump -uroot -p --all-databases &gt; /backup/mysqldump/all.sql</span><br></pre></td></tr></table></figure>

<p>备份指定数据库：</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqldump -uroot -p test &gt; /backup/mysqldump/test.sql</span><br></pre></td></tr></table></figure>

<p>备份指定数据库指定表(多个表以空格间隔)</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqldump -uroot -p  mysql db event &gt; /backup/mysqldump/<span class="number">2</span>table.sql</span><br></pre></td></tr></table></figure>

<p>备份指定数据库排除某些表</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 &gt; /backup/mysqldump/test2.sql</span><br></pre></td></tr></table></figure>

<h2 id="三、还原命令"><a href="#三、还原命令" class="headerlink" title="三、还原命令"></a>三、还原命令</h2><h3 id="3-1-系统行命令"><a href="#3-1-系统行命令" class="headerlink" title="3.1 系统行命令"></a>3.1 系统行命令</h3><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">mysqladmin -uroot -p create db_name </span><br><span class="line">mysql -uroot -p  db_name &lt; /backup/mysqldump/db_name.sql</span><br><span class="line"></span><br><span class="line">注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与db_name.sql中数据库名是一样的才可以导入。</span><br></pre></td></tr></table></figure>

<h3 id="3-2-soure-方法"><a href="#3-2-soure-方法" class="headerlink" title="3.2 soure 方法"></a>3.2 soure 方法</h3><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql &gt; use db_name</span><br><span class="line">mysql &gt; source /backup/mysqldump/db_name.sql</span><br></pre></td></tr></table></figure> 
      <!-- reward -->
      
    </div>
    

    <!-- copyright -->
    
    <footer class="article-footer">
       
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/" rel="tag">mysql</a></li></ul>

    </footer>
  </div>

    
 
   
</article>

    
    <article
  id="post-mysql/三种 MySQL 大表优化方案"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h2 itemprop="name">
  <a class="article-title" href="/2020/11/11/mysql/%E4%B8%89%E7%A7%8D%20MySQL%20%E5%A4%A7%E8%A1%A8%E4%BC%98%E5%8C%96%E6%96%B9%E6%A1%88/"
    >三种 MySQL 大表优化方案.md</a> 
</h2>
 

    </header>
     
    <div class="article-meta">
      <a href="/2020/11/11/mysql/%E4%B8%89%E7%A7%8D%20MySQL%20%E5%A4%A7%E8%A1%A8%E4%BC%98%E5%8C%96%E6%96%B9%E6%A1%88/" class="article-date">
  <time datetime="2020-11-10T16:00:00.000Z" itemprop="datePublished">2020-11-11</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/mysql/">mysql</a>
  </div>
   
    </div>
      
    <div class="article-entry" itemprop="articleBody">
       
  <h1 id="三种-MySQL-大表优化方案"><a href="#三种-MySQL-大表优化方案" class="headerlink" title="三种 MySQL 大表优化方案"></a>三种 MySQL 大表优化方案</h1><h1 id="问题概述"><a href="#问题概述" class="headerlink" title="问题概述"></a>问题概述</h1><p>使用阿里云rds for MySQL数据库（就是MySQL5.6版本），有个用户上网记录表6个月的数据量近2000万，保留最近一年的数据量达到4000万，查询速度极慢，日常卡死。严重影响业务。</p>
<p>问题前提：老系统，当时设计系统的人大概是大学没毕业，表设计和sql语句写的不仅仅是垃圾，简直无法直视。原开发人员都已离职，到我来维护，这就是传说中的维护不了就跑路，然后我就是掉坑的那个！！！</p>
<p>我尝试解决该问题，so，有个这个日志。</p>
<h1 id="方案概述"><a href="#方案概述" class="headerlink" title="方案概述"></a>方案概述</h1><p>方案一：优化现有mysql数据库。优点：不影响现有业务，源程序不需要修改代码，成本最低。缺点：有优化瓶颈，数据量过亿就玩完了。</p>
<p>方案二：升级数据库类型，换一种100%兼容mysql的数据库。优点：不影响现有业务，源程序不需要修改代码，你几乎不需要做任何操作就能提升数据库性能，缺点：多花钱</p>
<p>方案三：一步到位，大数据解决方案，更换newsql/nosql数据库。优点：扩展性强，成本低，没有数据容量瓶颈，缺点：需要修改源程序代码</p>
<p>以上三种方案，按顺序使用即可，数据量在亿级别一下的没必要换nosql，开发成本太高。三种方案我都试了一遍，而且都形成了落地解决方案。该过程心中慰问跑路的那几个开发者一万遍 :)</p>
<h1 id="方案一详细说明：优化现有mysql数据库"><a href="#方案一详细说明：优化现有mysql数据库" class="headerlink" title="方案一详细说明：优化现有mysql数据库"></a>方案一详细说明：优化现有mysql数据库</h1><p>跟阿里云数据库大佬电话沟通 and Google解决方案 and 问群里大佬，总结如下（都是精华）：</p>
<p>1.数据库设计和表创建时就要考虑性能</p>
<p>2.sql的编写需要注意优化</p>
<p>3.分区</p>
<p>4.分表</p>
<p>5.分库</p>
<p><strong>1.数据库设计和表创建时就要考虑性能</strong></p>
<p>mysql数据库本身高度灵活，造成性能不足，严重依赖开发人员能力。也就是说开发人员能力高，则mysql性能高。这也是很多关系型数据库的通病，所以公司的dba通常工资巨高。</p>
<p><strong>设计表时要注意：</strong></p>
<p>1.表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。</p>
<p>2.尽量使用INT而非BIGINT，如果非负则加上UNSIGNED（这样数值容量会扩大一倍），当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。</p>
<p>3.使用枚举或整数代替字符串类型</p>
<p>4.尽量使用TIMESTAMP而非DATETIME</p>
<p>5.单表不要有太多字段，建议在20以内</p>
<p>6.用整型来存IP</p>
<p><strong>索引</strong></p>
<p>1.索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描</p>
<p>2.应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描</p>
<p>3.值分布很稀少的字段不适合建索引，例如”性别”这种只有两三个值的字段</p>
<p>4.字符字段只建前缀索引</p>
<p>5.字符字段最好不要做主键</p>
<p>6.不用外键，由程序保证约束</p>
<p>7.尽量不用UNIQUE，由程序保证约束</p>
<p>8.使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引</p>
<p><strong>简言之就是使用合适的数据类型，选择合适的索引</strong></p>
<p>选择合适的数据类型（1）使用可存下数据的最小的数据类型，整型 &lt; date,time &lt; char,varchar &lt; blob（2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数（3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar（4）尽可能使用not null定义字段（5）尽量少用text，非用不可最好分表# 选择合适的索引列（1）查询频繁的列，在where，group by，order by，on从句中出现的列（2）where条件中&lt;，&lt;=，=，&gt;，&gt;=，between，in，以及like 字符串+通配符（%）出现的列（3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好（4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高：</p>
<p>原开发人员已经跑路，该表早已建立，我无法修改，故：该措辞无法执行，放弃！</p>
<p><strong>2.sql的编写需要注意优化</strong></p>
<p>1.使用limit对查询结果的记录进行限定</p>
<p>2.避免select *，将需要查找的字段列出来</p>
<p>3.使用连接（join）来代替子查询</p>
<p>4.拆分大的delete或insert语句</p>
<p>5.可通过开启慢查询日志来找出较慢的SQL</p>
<p>6.不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边</p>
<p>7.sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库</p>
<p>8.OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内</p>
<p>9.不用函数和触发器，在应用程序实现</p>
<p>10.避免%xxx式查询</p>
<p>11.少用JOIN</p>
<p>12.使用同类型进行比较，比如用’123’和’123’比，123和123比</p>
<p>13.尽量避免在WHERE子句中使用!=或&lt;&gt;操作符，否则将引擎放弃使用索引而进行全表扫描</p>
<p>14.对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5</p>
<p>15.列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大</p>
<p>原开发人员已经跑路，程序已经完成上线，我无法修改sql，故：该措辞无法执行，放弃！</p>
<h1 id="引擎"><a href="#引擎" class="headerlink" title="引擎"></a>引擎</h1><p>引擎</p>
<p>目前广泛使用的是MyISAM和InnoDB两种引擎：</p>
<p>MyISAM</p>
<p>MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：</p>
<p>1.不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁</p>
<p>2.不支持事务</p>
<p>3.不支持外键</p>
<p>4.不支持崩溃后的安全恢复</p>
<p>5.在表有读取查询的同时，支持往表中插入新纪录</p>
<p>6.支持BLOB和TEXT的前500个字符索引，支持全文索引</p>
<p>7.支持延迟更新索引，极大提升写入性能</p>
<p>8.对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用</p>
<p>InnoDB</p>
<p>InnoDB在MySQL 5.5后成为默认索引，它的特点是：</p>
<p>1.支持行锁，采用MVCC来支持高并发</p>
<p>2.支持事务</p>
<p>3.支持外键</p>
<p>4.支持崩溃后的安全恢复</p>
<p>5.不支持全文索引</p>
<p><strong>总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表</strong></p>
<p>MyISAM速度可能超快，占用存储空间也小，但是程序要求事务支持，故InnoDB是必须的，故该方案无法执行，放弃！</p>
<p><strong>3.分区</strong></p>
<p>MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码</p>
<p>对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引</p>
<p>用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化，我测试，查询时不带分区条件的列，也会提高速度，故该措施值得一试。</p>
<p><strong>分区的好处是：</strong></p>
<p>1.可以让单表存储更多的数据</p>
<p>2.分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作</p>
<p>3.部分查询能够从查询条件确定只落在少数分区上，速度会很快</p>
<p>4.分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备</p>
<p>5.可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争</p>
<p>6.可以备份和恢复单个分区</p>
<p><strong>分区的限制和缺点：</strong></p>
<p>1.一个表最多只能有1024个分区</p>
<p>2.如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来</p>
<p>3.分区表无法使用外键约束</p>
<p>4.NULL值会使分区过滤无效</p>
<p>5.所有分区必须使用相同的存储引擎</p>
<p><strong>分区的类型：</strong></p>
<p>1.RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区</p>
<p>2.LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择</p>
<p>3.HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式</p>
<p>4.KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值</p>
<p>5.具体关于mysql分区的概念请自行google或查询官方文档，我这里只是抛砖引玉了。</p>
<p>我首先根据月份把上网记录表RANGE分区了12份，查询效率提高6倍左右，效果不明显，故：换id为HASH分区，分了64个分区，查询速度提升显著。问题解决！</p>
<p>结果如下：PARTITION BY HASH (id)PARTITIONS 64</p>
<p>select count(<strong>) from readroom_website; –11901336行记录</strong></p>
<p><strong>/</strong> 受影响行数: 0 已找到记录: 1 警告: 0 持续时间 1 查询: 5.734 sec. <strong>/</strong></p>
<p><strong>select * from readroom_website where month(accesstime) =11 limit 10;</strong></p>
<p><strong>/</strong> 受影响行数: 0 已找到记录: 10 警告: 0 持续时间 1 查询: 0.719 sec. */</p>
<p><strong>4.分表</strong></p>
<p>分表就是把一张大表，按照如上过程都优化了，还是查询卡死，那就把这个表分成多张表，把一次查询分成多次查询，然后把结果组合返回给用户。</p>
<p>分表分为垂直拆分和水平拆分，通常以某个字段做拆分项。比如以id字段拆分为100张表：表名为 tableName_id%100</p>
<p>但：分表需要修改源程序代码，会给开发带来大量工作，极大的增加了开发成本，故：只适合在开发初期就考虑到了大量数据存在，做好了分表处理，不适合应用上线了再做修改，成本太高！！！而且选择这个方案，都不如选择我提供的第二第三个方案的成本低！故不建议采用。</p>
<p><strong>5.分库</strong></p>
<p>把一个数据库分成多个，建议做个读写分离就行了，真正的做分库也会带来大量的开发成本，得不偿失！不推荐使用。</p>
<h1 id="方案二详细说明：升级数据库，换一个100-兼容mysql的数据库"><a href="#方案二详细说明：升级数据库，换一个100-兼容mysql的数据库" class="headerlink" title="方案二详细说明：升级数据库，换一个100%兼容mysql的数据库"></a>方案二详细说明：升级数据库，换一个100%兼容mysql的数据库</h1><p>mysql性能不行，那就换个。为保证源程序代码不修改，保证现有业务平稳迁移，故需要换一个100%兼容mysql的数据库。</p>
<p>开源选择</p>
<p>1.tiDB <a target="_blank" rel="noopener" href="https://github.com/pingcap/tidb">https://github.com/pingcap/tidb</a></p>
<p>2.Cubrid <a target="_blank" rel="noopener" href="https://www.cubrid.org/">https://www.cubrid.org/</a></p>
<p>3.开源数据库会带来大量的运维成本且其工业品质和MySQL尚有差距，有很多坑要踩，如果你公司要求必须自建数据库，那么选择该类型产品。</p>
<p>云数据选择</p>
<p>1.阿里云POLARDB</p>
<p>2.<a target="_blank" rel="noopener" href="https://www.aliyun.com/product/polardb?spm=a2c4g.11174283.cloudEssentials.47.7a984b5cS7h4wH">https://www.aliyun.com/product/polardb?spm=a2c4g.11174283.cloudEssentials.47.7a984b5cS7h4wH</a></p>
<p>官方介绍语：POLARDB 是阿里云自研的下一代关系型分布式云原生数据库，100%兼容MySQL，存储容量最高可达 100T，性能最高提升至 MySQL 的 6 倍。POLARDB 既融合了商业数据库稳定、可靠、高性能的特征，又具有开源数据库简单、可扩展、持续迭代的优势，而成本只需商用数据库的 1/10。</p>
<p>我开通测试了一下，支持免费mysql的数据迁移，无操作成本，性能提升在10倍左右，价格跟rds相差不多，是个很好的备选解决方案！</p>
<p>1.阿里云OcenanBase</p>
<p>2.淘宝使用的，扛得住双十一，性能卓著，但是在公测中，我无法尝试，但值得期待</p>
<p>3.阿里云HybridDB for MySQL (原PetaData)</p>
<p>4.<a target="_blank" rel="noopener" href="https://www.aliyun.com/product/petadata?spm=a2c4g.11174283.cloudEssentials.54.7a984b5cS7h4wH">https://www.aliyun.com/product/petadata?spm=a2c4g.11174283.cloudEssentials.54.7a984b5cS7h4wH</a></p>
<p>官方介绍：云数据库HybridDB for MySQL （原名PetaData）是同时支持海量数据在线事务（OLTP）和在线分析（OLAP）的HTAP（Hybrid Transaction/Analytical Processing）关系型数据库。</p>
<p>我也测试了一下，是一个olap和oltp兼容的解决方案，但是价格太高，每小时高达10块钱，用来做存储太浪费了，适合存储和分析一起用的业务。</p>
<p>1.腾讯云DCDB</p>
<p>2.<a target="_blank" rel="noopener" href="https://cloud.tencent.com/product/dcdb_for_tdsql">https://cloud.tencent.com/product/dcdb_for_tdsql</a></p>
<p>官方介绍：DCDB又名TDSQL，一种兼容MySQL协议和语法，支持自动水平拆分的高性能分布式数据库——即业务显示为完整的逻辑表，数据却均匀的拆分到多个分片中；每个分片默认采用主备架构，提供灾备、恢复、监控、不停机扩容等全套解决方案，适用于TB或PB级的海量数据场景。</p>
<p>腾讯的我不喜欢用，不多说。原因是出了问题找不到人，线上问题无法解决头疼！但是他价格便宜，适合超小公司，玩玩。</p>
<h1 id="方案三详细说明：去掉mysql，换大数据引擎处理数据"><a href="#方案三详细说明：去掉mysql，换大数据引擎处理数据" class="headerlink" title="方案三详细说明：去掉mysql，换大数据引擎处理数据"></a>方案三详细说明：去掉mysql，换大数据引擎处理数据</h1><p>数据量过亿了，没得选了，只能上大数据了。</p>
<p>开源解决方案</p>
<p>hadoop家族。hbase/hive怼上就是了。但是有很高的运维成本，一般公司是玩不起的，没十万投入是不会有很好的产出的！</p>
<p>云解决方案</p>
<p>这个就比较多了，也是一种未来趋势，大数据由专业的公司提供专业的服务，小公司或个人购买服务，大数据就像水/电等公共设施一样，存在于社会的方方面面。</p>
<p>国内做的最好的当属阿里云。</p>
<p>我选择了阿里云的MaxCompute配合DataWorks，使用超级舒服，按量付费，成本极低。</p>
<p>MaxCompute可以理解为开源的Hive，提供sql/mapreduce/ai算法/python脚本/shell脚本等方式操作数据，数据以表格的形式展现，以分布式方式存储，采用定时任务和批处理的方式处理数据。DataWorks提供了一种工作流的方式管理你的数据处理任务和调度监控。</p>
<p>当然你也可以选择阿里云hbase等其他产品，我这里主要是离线处理，故选择MaxCompute，基本都是图形界面操作，大概写了300行sql，费用不超过100块钱就解决了数据处理问题。</p>
 
      <!-- reward -->
      
    </div>
    

    <!-- copyright -->
    
    <footer class="article-footer">
       
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/" rel="tag">mysql</a></li></ul>

    </footer>
  </div>

    
 
   
</article>

    
    <article
  id="post-mysql/必须了解的mysql三大日志-binlog、redo log和undo log"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h2 itemprop="name">
  <a class="article-title" href="/2020/11/11/mysql/%E5%BF%85%E9%A1%BB%E4%BA%86%E8%A7%A3%E7%9A%84mysql%E4%B8%89%E5%A4%A7%E6%97%A5%E5%BF%97-binlog%E3%80%81redo%20log%E5%92%8Cundo%20log/"
    >必须了解的mysql三大日志-binlog、redo log和undo log.md</a> 
</h2>
 

    </header>
     
    <div class="article-meta">
      <a href="/2020/11/11/mysql/%E5%BF%85%E9%A1%BB%E4%BA%86%E8%A7%A3%E7%9A%84mysql%E4%B8%89%E5%A4%A7%E6%97%A5%E5%BF%97-binlog%E3%80%81redo%20log%E5%92%8Cundo%20log/" class="article-date">
  <time datetime="2020-11-10T16:00:00.000Z" itemprop="datePublished">2020-11-11</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/mysql/">mysql</a>
  </div>
   
    </div>
      
    <div class="article-entry" itemprop="articleBody">
       
  <h1 id="必须了解的mysql三大日志-binlog、redo-log和undo-log"><a href="#必须了解的mysql三大日志-binlog、redo-log和undo-log" class="headerlink" title="必须了解的mysql三大日志-binlog、redo log和undo log"></a>必须了解的mysql三大日志-binlog、redo log和undo log</h1><p>日志是 <code>mysql</code> 数据库的重要组成部分，记录着数据库运行期间各种状态信息。<code>mysql</code>日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发，我们重点需要关注的是二进制日志( <code>binlog</code> )和事务日志(包括<br><code>redo log</code> 和 <code>undo log</code> )，本文接下来会详细介绍这三种日志。</p>
<h2 id="binlog"><a href="#binlog" class="headerlink" title="binlog"></a>binlog</h2><p><code>binlog</code> 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。<code>binlog</code> 是 <code>mysql</code>的逻辑日志，并且由 <code>Server</code> 层进行记录，使用任何存储引擎的 <code>mysql</code> 数据库都会记录 <code>binlog</code> 日志。</p>
<ul>
<li><strong>逻辑日志</strong>：可以简单理解为记录的就是sql语句 。</li>
<li><strong>物理日志</strong>：<code>mysql</code> 数据最终是保存在数据页中的，物理日志记录的就是数据页变更 。</li>
</ul>
<p><code>binlog</code> 是通过追加的方式进行写入的，可以通过 <code>max_binlog_size</code> 参数设置每个 <code>binlog</code><br>文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。</p>
<h3 id="binlog使用场景"><a href="#binlog使用场景" class="headerlink" title="binlog使用场景"></a>binlog使用场景</h3><p>在实际应用中， <code>binlog</code> 的主要使用场景有两个，分别是 <strong>主从复制</strong> 和 <strong>数据恢复</strong>。</p>
<ol>
<li><strong>主从复制</strong> ：在 <code>Master</code> 端开启 <code>binlog</code> ，然后将 <code>binlog</code> 发送到各个<code>Slave</code> 端， <code>Slave</code> 端重放 <code>binlog</code> 从而达到主从数据一致。</li>
<li><strong>数据恢复</strong> ：通过使用 <code>mysqlbinlog</code> 工具来恢复数据。</li>
</ol>
<h3 id="binlog刷盘时机"><a href="#binlog刷盘时机" class="headerlink" title="binlog刷盘时机"></a>binlog刷盘时机</h3><p>对于 <code>InnoDB</code> 存储引擎而言，只有在事务提交时才会记录 <code>biglog</code> ，此时记录还在内存中，那么 <code>biglog</code><br>是什么时候刷到磁盘中的呢？<code>mysql</code> 通过 <code>sync_binlog</code> 参数控制 <code>biglog</code> 的刷盘时机，取值范围是 <code>0-N</code><br>：</p>
<ul>
<li>0：不去强制要求，由系统自行判断何时写入磁盘；</li>
<li>1：每次 <code>commit</code> 的时候都要将 <code>binlog</code> 写入磁盘；</li>
<li>N：每N个事务，才会将 <code>binlog</code> 写入磁盘。</li>
</ul>
<p>从上面可以看出， <code>sync_binlog</code> 最安全的是设置是 <code>1</code> ，这也是 <code>MySQL 5.7.7</code><br>之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。</p>
<h3 id="binlog日志格式"><a href="#binlog日志格式" class="headerlink" title="binlog日志格式"></a>binlog日志格式</h3><p><code>binlog</code> 日志有三种格式，分别为 <code>STATMENT</code> 、 <code>ROW</code> 和 <code>MIXED</code> 。</p>
<blockquote>
<p>在 <code>MySQL 5.7.7</code> 之前，默认的格式是 <code>STATEMENT</code> ， <code>MySQL 5.7.7</code> 之后，默认值是 <code>ROW</code>。日志格式通过 <code>binlog-format</code> 指定。</p>
</blockquote>
<ul>
<li><p><code>STATMENT</code>：基于<code>SQL</code> 语句的复制( <code>statement-based replication, SBR</code> )，每一条会修改数据的sql语句会记录到 <code>binlog</code> 中  。</p>
</li>
<li><ul>
<li>优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO  , 从而提高了性能；</li>
<li>缺点：在某些情况下会导致主从数据不一致，比如执行sysdate() 、  slepp()  等 。</li>
</ul>
</li>
<li><p><code>ROW</code>：基于行的复制(<code>row-based replication, RBR</code> )，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了 。</p>
</li>
<li><ul>
<li>优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题 ；</li>
<li>缺点：会产生大量的日志，尤其是<code>alter table</code> 的时候会让日志暴涨</li>
</ul>
</li>
<li><p><code>MIXED</code>：基于<code>STATMENT</code> 和 <code>ROW</code> 两种模式的混合复制( <code>mixed-based replication, MBR</code> )，一般的复制使用 <code>STATEMENT</code> 模式保存 <code>binlog</code> ，对于 <code>STATEMENT</code> 模式无法复制的操作使用 <code>ROW</code> 模式保存 <code>binlog</code></p>
</li>
</ul>
<h2 id="redo-log"><a href="#redo-log" class="headerlink" title="redo log"></a>redo log</h2><h3 id="为什么需要redo-log"><a href="#为什么需要redo-log" class="headerlink" title="为什么需要redo log"></a>为什么需要redo log</h3><p>我们都知道，事务的四大特性里面有一个是 <strong>持久性</strong> ，具体来说就是<br><strong>只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态</strong> 。那么 <code>mysql</code><br>是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：</p>
<ol>
<li>因为 <code>Innodb</code> 是以 <code>页</code> 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！</li>
<li>一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！</li>
</ol>
<p>因此 <code>mysql</code> 设计了 <code>redo log</code> ， <strong>具体来说就是只记录事务对数据页做了哪些修改</strong><br>，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。</p>
<h3 id="redo-log基本概念"><a href="#redo-log基本概念" class="headerlink" title="redo log基本概念"></a>redo log基本概念</h3><p><code>redo log</code> 包括两部分：一个是内存中的日志缓冲( <code>redo log buffer</code> )，另一个是磁盘上的日志文件( <code>redo log file</code> )。<code>mysql</code> 每执行一条 <code>DML</code> 语句，先将记录写入 <code>redo log buffer</code><br>，后续某个时间点再一次性将多个操作记录写到 <code>redo log file</code> 。这种 <strong>先写日志，再写磁盘</strong> 的技术就是 <code>MySQL</code><br>里经常说到的 <code>WAL(Write-Ahead Logging)</code> 技术。</p>
<p>在计算机操作系统中，用户空间( <code>user space</code> )下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间( <code>kernel space</code> )缓冲区(<code>OS Buffer</code> )。因此， <code>redo log buffer</code> 写入 <code>redo log file</code> 实际上是先写入 <code>OS Buffer</code> ，然后再通过系统调用 <code>fsync()</code> 将其刷到 <code>redo log file</code><br>中，过程如下：</p>
<p><img src="http://iubest.gitee.io/pic/640-1601018297678.webp" alt="img"></p>
<p><code>mysql</code> 支持三种将 <code>redo log buffer</code> 写入 <code>redo log file</code> 的时机，可以通过 <code>innodb_flush_log_at_trx_commit</code> 参数配置，各参数值含义如下：</p>
<table>
<thead>
<tr>
<th align="left">参数值</th>
<th align="left">含义</th>
</tr>
</thead>
<tbody><tr>
<td align="left">0（延迟写）</td>
<td align="left">事务提交时不会将 <code>redo log buffer</code> 中日志写入到 <code>os buffer</code> ，而是每秒写入 <code>os buffer</code> 并调用 <code>fsync()</code> 写入到 <code>redo log file</code> 中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。</td>
</tr>
<tr>
<td align="left">1（实时写，实时刷）</td>
<td align="left">事务每次提交都会将 <code>redo log buffer</code> 中的日志写入 <code>os buffer</code>并调用 <code>fsync()</code> 刷到 <code>redo log file</code> 中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。</td>
</tr>
<tr>
<td align="left">2（实时写，延迟刷）</td>
<td align="left">每次提交都仅写入到 <code>os buffer</code> ，然后是每秒调用 <code>fsync()</code> 将 <code>os buffer</code>中的日志写入到 <code>redo log file</code> 。</td>
</tr>
</tbody></table>
<p><img src="http://iubest.gitee.io/pic/640-1601018297764.webp" alt="img"></p>
<h3 id="redo-log记录形式"><a href="#redo-log记录形式" class="headerlink" title="redo log记录形式"></a>redo log记录形式</h3><p>前面说过， <code>redo log</code> 实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此 <code>redo log</code><br>实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。如下图：</p>
<p><img src="http://iubest.gitee.io/pic/640-1601018297595.webp" alt="img"></p>
<p>同时我们很容易得知， 在innodb中，既有<code>redo log</code> 需要刷盘，还有 <code>数据页</code> 也需要刷盘， <code>redo log</code>存在的意义主要就是降低对 <code>数据页</code> 刷盘的要求 ** 。在上图中， <code>write pos</code> 表示 <code>redo log</code> 当前记录的 <code>LSN</code> (逻辑序列号)位置，<code>check point</code> 表示 <strong>数据页更改记录</strong> 刷盘后对应 <code>redo log</code> 所处的 <code>LSN</code>(逻辑序列号)位置。<code>write pos</code> 到 <code>check point</code> 之间的部分是 <code>redo log</code> 空着的部分，用于记录新的记录；<code>check point</code> 到 <code>write pos</code> 之间是 <code>redo log</code> 待落盘的数据页更改记录。当 <code>write pos</code>追上 <code>check point</code> 时，会先推动 <code>check point</code> 向前移动，空出位置再记录新的日志。</p>
<p>启动 <code>innodb</code> 的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为 <code>redo log</code>记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如 <code>binlog</code> )要快很多。重启 <code>innodb</code> 时，首先会检查磁盘中数据页的 <code>LSN</code>，如果数据页的 <code>LSN</code> 小于日志中的 <code>LSN</code> ，则会从 <code>checkpoint</code> 开始恢复。还有一种情况，在宕机前正处于<br><code>checkpoint</code> 的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的 <code>LSN</code> 大于日志中的 <code>LSN</code><br>，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。</p>
<h3 id="redo-log与binlog区别"><a href="#redo-log与binlog区别" class="headerlink" title="redo log与binlog区别"></a>redo log与binlog区别</h3><table>
<thead>
<tr>
<th align="left"></th>
<th align="left">redo log</th>
<th align="left">binlog</th>
</tr>
</thead>
<tbody><tr>
<td align="left">文件大小</td>
<td align="left"><code>redo log</code> 的大小是固定的。</td>
<td align="left"><code>binlog</code> 可通过配置参数 <code>max_binlog_size</code> 设置每个<code>binlog</code> 文件的大小。</td>
</tr>
<tr>
<td align="left">实现方式</td>
<td align="left"><code>redo log</code> 是 <code>InnoDB</code> 引擎层实现的，并不是所有引擎都有。</td>
<td align="left"><code>binlog</code> 是 <code>Server</code> 层实现的，所有引擎都可以使用 <code>binlog</code> 日志</td>
</tr>
<tr>
<td align="left">记录方式</td>
<td align="left">redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。</td>
<td align="left">binlog通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上</td>
</tr>
<tr>
<td align="left">适用场景</td>
<td align="left"><code>redo log</code> 适用于崩溃恢复(crash-safe)</td>
<td align="left"><code>binlog</code> 适用于主从复制和数据恢复</td>
</tr>
</tbody></table>
<p>由 <code>binlog</code> 和 <code>redo log</code> 的区别可知：<code>binlog</code> 日志只用于归档，只依靠<code>binlog</code> 是没有 <code>crash-safe</code> 能力的。但只有 <code>redo log</code> 也不行，因为 <code>redo log</code> 是 <code>InnoDB</code><br>特有的，且日志上的记录落盘后会被覆盖掉。因此需要 <code>binlog</code> 和 <code>redo log</code><br>二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。</p>
<h2 id="undo-log"><a href="#undo-log" class="headerlink" title="undo log"></a>undo log</h2><p>数据库事务四大特性中有一个是 <strong>原子性</strong> ，具体来说就是 <strong>原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况</strong><br>。实际上， <strong>原子性</strong> 底层就是通过 <code>undo log</code> 实现的。<code>undo log</code> 主要记录了数据的逻辑变化，比如一条 <code>INSERT</code> 语句，对应一条 <code>DELETE</code> 的 <code>undo log</code> ，对于每个 <code>UPDATE</code> 语句，对应一条相反的 <code>UPDATE</code> 的 <code>undo log</code> ，这样在发生错误时，就能回滚到事务之前的数据状态。同时， <code>undo log</code> 也是 <code>MVCC</code>(多版本并发控制)实现的关键，这部分内容在  面试中的老大难-mysql事务和锁，一次性讲清楚！中有介绍，不再赘述。</p>
<p>- END -</p>
 
      <!-- reward -->
      
    </div>
    

    <!-- copyright -->
    
    <footer class="article-footer">
       
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/" rel="tag">mysql</a></li></ul>

    </footer>
  </div>

    
 
   
</article>

    
    <article
  id="post-mysql/神奇的 SQL，Group By"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h2 itemprop="name">
  <a class="article-title" href="/2020/11/11/mysql/%E7%A5%9E%E5%A5%87%E7%9A%84%20SQL%EF%BC%8CGroup%20By/"
    >神奇的 SQL，Group By.md</a> 
</h2>
 

    </header>
     
    <div class="article-meta">
      <a href="/2020/11/11/mysql/%E7%A5%9E%E5%A5%87%E7%9A%84%20SQL%EF%BC%8CGroup%20By/" class="article-date">
  <time datetime="2020-11-10T16:00:00.000Z" itemprop="datePublished">2020-11-11</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/mysql/">mysql</a>
  </div>
   
    </div>
      
    <div class="article-entry" itemprop="articleBody">
       
  <h1 id="神奇的-SQL，Group-By"><a href="#神奇的-SQL，Group-By" class="headerlink" title="神奇的 SQL，Group By"></a>神奇的 SQL，Group By</h1><h2 id="GROUP-BY-后-SELECT-列的限制"><a href="#GROUP-BY-后-SELECT-列的限制" class="headerlink" title="GROUP BY 后 SELECT 列的限制"></a><strong>GROUP BY 后 SELECT 列的限制</strong></h2><p>标准 SQL 规定，在对表进行聚合查询的时候，只能在 SELECT 子句中写下面 3 种内容：通过 GROUP BY 子句指定的聚合键、聚合函数（SUM 、AVG 等）、常量。我们来看个例子：</p>
<p>我们有 学生班级表（tbl_student_class） 以及 数据如下 ：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line">DROP TABLE IF EXISTS tbl_student_class;</span><br><span class="line">CREATE TABLE tbl_student_class (</span><br><span class="line">  id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT &#39;自增主键&#39;,</span><br><span class="line">  sno varchar(12) NOT NULL COMMENT &#39;学号&#39;,</span><br><span class="line">  cno varchar(5) NOT NULL COMMENT &#39;班级号&#39;,</span><br><span class="line">  cname varchar(20) NOT NULL COMMENT &#39;班级名&#39;,</span><br><span class="line">  PRIMARY KEY (id)</span><br><span class="line">) COMMENT&#x3D;&#39;学生班级表&#39;;</span><br><span class="line"></span><br><span class="line">-- ----------------------------</span><br><span class="line">-- Records of tbl_student_class</span><br><span class="line">-- ----------------------------</span><br><span class="line">INSERT INTO tbl_student_class VALUES (&#39;1&#39;, &#39;20190607001&#39;, &#39;0607&#39;, &#39;影视7班&#39;);</span><br><span class="line">INSERT INTO tbl_student_class VALUES (&#39;2&#39;, &#39;20190607002&#39;, &#39;0607&#39;, &#39;影视7班&#39;);</span><br><span class="line">INSERT INTO tbl_student_class VALUES (&#39;3&#39;, &#39;20190608003&#39;, &#39;0608&#39;, &#39;影视8班&#39;);</span><br><span class="line">INSERT INTO tbl_student_class VALUES (&#39;4&#39;, &#39;20190608004&#39;, &#39;0608&#39;, &#39;影视8班&#39;);</span><br><span class="line">INSERT INTO tbl_student_class VALUES (&#39;5&#39;, &#39;20190609005&#39;, &#39;0609&#39;, &#39;影视9班&#39;);</span><br><span class="line">INSERT INTO tbl_student_class VALUES (&#39;6&#39;, &#39;20190609006&#39;, &#39;0609&#39;, &#39;影视9班&#39;);</span><br></pre></td></tr></table></figure>

<p>我们想统计各个班（班级号、班级名）一个有多少人、以及最大的学号，我们该怎么写这个查询 SQL ？我想大家应该都会</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT cno,cname,count(sno),MAX(sno) </span><br><span class="line">FROM tbl_student_class</span><br><span class="line">GROUP BY cno,cname;</span><br></pre></td></tr></table></figure>

<p>可是有人会想了，cno 和 cname 本来就是一对一，cno 一旦确定，cname 也就确定了，那 SQL 是不是可以这么写 ？</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SELECT cno,cname,count(sno),MAX(sno) </span><br><span class="line">FROM tbl_student_class</span><br><span class="line">GROUP BY cno;</span><br></pre></td></tr></table></figure>

<p>执行报错了：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column &#39;test.tbl_student_class.cname&#39; which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode&#x3D;only_full_group_by</span><br></pre></td></tr></table></figure>

<p>提示信息：SELECT 列表中的第二个表达式（cname）不在 GROUP BY 的子句中，同时它也不是聚合函数；这与 sql 模式：ONLY_FULL_GROUP_BY 不相容。</p>
<p><strong>为什么 GROUP BY 之后不能直接引用原表（不在 GROUP BY 子句）中的列 ？</strong>莫急，我们慢慢往下看。</p>
<h2 id="SQL-模式"><a href="#SQL-模式" class="headerlink" title="SQL 模式"></a><strong>SQL 模式</strong></h2><p>MySQL 服务器可以在不同的 SQL 模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于 sql_mode 系统变量的值。DBA 可以设置全局SQL模式以匹配站点服务器操作要求，并且每个应用程序可以将其会话 SQL 模式设置为其自己的要求。</p>
<p>模式会影响 MySQL 支持的 SQL 语法以及它执行的 数据验证检查，这使得在不同环境中使用MySQL以及将MySQL与其他数据库服务器一起使用变得更加容易。更多详情请查阅官网：Server SQL Modes。</p>
<p>MySQL 版本不同，内容会略有不同（包括默认值），查阅的时候注意与自身的 MySQL 版本保持一致。</p>
<p>SQL 模式主要分两类：语法支持类和数据检查类，常用的如下</p>
<h3 id="语法支持类"><a href="#语法支持类" class="headerlink" title="语法支持类　　　　"></a><strong>语法支持类</strong>　　　　</h3><ul>
<li><code>ONLY_FULL_GROUP_BY</code></li>
</ul>
<p>对于 GROUP BY 聚合操作，如果在 SELECT 中的列、HAVING 或者 ORDER BY 子句的列，没有在GROUP BY中出现，那么这个SQL是不合法的</p>
<ul>
<li><code>ANSI_QUOTES</code></li>
</ul>
<p>启用 ANSI_QUOTES 后，不能用双引号来引用字符串，因为它被解释为识别符，作用与 ` 一样。设置它以后，update t set f1=”” …，会报 Unknown column ‘’ in field list 这样的语法错误</p>
<ul>
<li><code>PIPES_AS_CONCAT</code></li>
</ul>
<p>将 || 视为字符串的连接操作符而非 或 运算符，这和Oracle数据库是一样的，也和字符串的拼接函数 CONCAT() 相类似</p>
<ul>
<li><code>NO_TABLE_OPTIONS</code></li>
</ul>
<p>使用 SHOW CREATE TABLE 时不会输出MySQL特有的语法部分，如 ENGINE ，这个在使用 mysqldump 跨DB种类迁移的时候需要考虑</p>
<ul>
<li><code>NO_AUTO_CREATE_USER</code></li>
</ul>
<p>字面意思不自动创建用户。在给MySQL用户授权时，我们习惯使用 GRANT … ON … TO dbuser 顺道一起创建用户。设置该选项后就与oracle操作类似，授权之前必须先建立用户</p>
<h3 id="数据检查类"><a href="#数据检查类" class="headerlink" title="数据检查类　"></a><strong>数据检查类</strong>　</h3><h3 id=""><a href="#" class="headerlink" title="　　"></a>　　</h3><ul>
<li><code>NO_ZERO_DATE</code></li>
</ul>
<p>认为日期 ‘0000-00-00’ 非法，与是否设置后面的严格模式有关</p>
<p>1、如果设置了严格模式，则 NO_ZERO_DATE 自然满足。但如果是 INSERT IGNORE 或 UPDATE IGNORE，’0000-00-00’依然允许且只显示warning；</p>
<p>2、如果在非严格模式下，设置了NO_ZERO_DATE，效果与上面一样，’0000-00-00’ 允许但显示warning；如果没有设置NO_ZERO_DATE，no warning，当做完全合法的值；</p>
<p>3、NO_ZERO_IN_DATE情况与上面类似，不同的是控制日期和天，是否可为 0 ，即 2010-01-00 是否合法；</p>
<ul>
<li><code>NO_ENGINE_SUBSTITUTION</code></li>
</ul>
<p>使用 ALTER TABLE 或 CREATE TABLE 指定 ENGINE 时， 需要的存储引擎被禁用或未编译，该如何处理。启用 NO_ENGINE_SUBSTITUTION 时，那么直接抛出错误；不设置此值时，CREATE用默认的存储引擎替代，ATLER不进行更改，并抛出一个 warning</p>
<ul>
<li><code>STRICT_TRANS_TABLES</code></li>
</ul>
<p>设置它，表示启用严格模式。注意 STRICT_TRANS_TABLES 不是几种策略的组合，单独指 INSERT、UPDATE 出现少值或无效值该如何处理：</p>
<p>1、前面提到的把 ‘’ 传给int，严格模式下非法，若启用非严格模式则变成 0，产生一个warning；</p>
<p>2、Out Of Range，变成插入最大边界值；</p>
<p>3、当要插入的新行中，不包含其定义中没有显式DEFAULT子句的非NULL列的值时，该列缺少值；</p>
<h3 id="默认模式"><a href="#默认模式" class="headerlink" title="默认模式"></a><strong>默认模式</strong></h3><p>当我们没有修改配置文件的情况下，MySQL 是有自己的默认模式的；版本不同，默认模式也不同</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">-- 查看 MySQL 版本</span><br><span class="line">SELECT VERSION();</span><br><span class="line"></span><br><span class="line">-- 查看 sql_mode</span><br><span class="line">SELECT @@sql_mode;</span><br></pre></td></tr></table></figure>

<p><img src="http://iubest.gitee.io/pic/640-1601015830415.gif" alt="img"></p>
<p>我们可以看到，5.7.21 的默认模式包含：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</span><br></pre></td></tr></table></figure>

<p>而第一个：<strong>ONLY_FULL_GROUP_BY 就会约束：当我们进行聚合查询的时候，SELECT 的列不能直接包含非 GROUP BY 子句中的列。</strong>那如果我们去掉该模式（从“严格模式”到“宽松模式”）呢 ？</p>
<p>搜索顶级架构师公众号回复“架构整洁”，送你一份惊喜礼包。</p>
<p><img src="http://iubest.gitee.io/pic/640-1601015830552.gif" alt="img"></p>
<p>我们发现，上述报错的 SQL</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">-- 宽松模式下 可以执行</span><br><span class="line">SELECT cno,cname,count(sno),MAX(sno) </span><br><span class="line">FROM tbl_student_class</span><br><span class="line">GROUP BY cno;</span><br></pre></td></tr></table></figure>

<p>能正常执行了，但是一般情况下不推荐这样配置，线上环境往往是“严格模式”，而不是“宽松模式”；虽然案例中，无论是“严格模式”，还是“宽松模式”，结果都是对的，那是因为 cno 与 cname 唯一对应的，如果 cno 与 cname 不是唯一对应，那么在“宽松模式下” cname 的值是随机的，这就会造成难以排查的问题，有兴趣的可以去试试。<strong>那为什么会有 ONLY_FULL_GROUP_BY 模式呢 ？</strong> 我们继续往下看</p>
<h2 id="阶"><a href="#阶" class="headerlink" title="阶"></a><strong>阶</strong></h2><p>阶（order）是用来区分集合或谓词的阶数的概念。谓词逻辑中，根据输入值的阶数对谓词进行分类。</p>
<p>= 或者 BETWEEEN 等输入值为一行的谓词叫作”一阶谓词”，而像 EXISTS 这样输入值为行的集合的谓词叫作”二阶谓词”（HAVING 的输入值也是集合，但它不是谓词）。以此类推，三阶谓词＝输入值为”集合的集合”的谓词，四阶谓词＝输入值为”集合的集合的集合”的谓词，但是 SQL 里并不会出现三阶以上的情况，所以不用太在意。</p>
<p>简单点如下图</p>
<p><img src="http://iubest.gitee.io/pic/640-1601015830381.webp" alt="img"></p>
<p>谈到了阶，就不得不谈下集合论；集合论是 SQL 语言的根基，因为它的这个特性，SQL 也被称为面向集合语言。只有从集合的角度来思考，才能明白 SQL 的强大威力。通过上图，相信大家也都能看到，这里不做更深入的讲解了，有兴趣的可以去查相关资料。</p>
<h2 id="为什么聚合后不能再引用原表中的列"><a href="#为什么聚合后不能再引用原表中的列" class="headerlink" title="为什么聚合后不能再引用原表中的列"></a><strong>为什么聚合后不能再引用原表中的列</strong></h2><p>很多人都知道聚合查询的限制，但是很少有人能正确地理解为什么会有这样的约束。表 tbl_student_class 中的 cname 存储的是每位学生的班级信息。</p>
<p>搜索顶级架构师公众号回复“Java”，送你一份Java面试题和答案惊喜礼包。</p>
<p>但需要注意的是，这里的 cname 只是每个学生的属性，并不是小组的属性，而 GROUP BY 又是聚合操作，操作的对象就是由多个学生组成的小组，因此，小组的属性只能是平均或者总和等统计性质的属性，如下图</p>
<p><img src="http://iubest.gitee.io/pic/640-1601015830411.webp" alt="img"></p>
<p>询问每个学生的 cname 是可以的，但是询问由多个学生组成的小组的 cname 就没有意义了。对于小组来说，只有”一共多少学生”或者”最大学号是多少？”这样的问法才是有意义的。</p>
<p>强行将适用于个体的属性套用于团体之上，纯粹是一种分类错误；而 GROUP BY 的作用是将一个个元素划分成若干个子集，使用 GROUP BY 聚合之后，SQL 的操作对象便由 0 阶的”行”变为了 1 阶的”行的集合”，此时，行的属性便不能使用了。</p>
<p>SQL 的世界其实是层级分明的等级社会，将低阶概念的属性用在高阶概念上会导致秩序的混乱，这是不允许的。此时我相信大家都明白：为什么聚合后不能再引用原表中的列 。</p>
<h2 id="单元素集合也是集合"><a href="#单元素集合也是集合" class="headerlink" title="单元素集合也是集合"></a><strong>单元素集合也是集合</strong></h2><p>现在的集合论认为单元素集合是一种正常的集合。单元素集合和空集一样，主要是为了保持理论的完整性而定义的。因此对于以集合论为基础的 SQL 来说，当然也需要严格地区分元素和单元素集合。因此，元素 a 和集合 {a} 之间存在着非常醒目的层级差别。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">a ≠ &#123;a&#125;</span><br></pre></td></tr></table></figure>

<p>这两个层级的区别分别对应着 SQL 中的 WHERE 子句和 HAVING 子句的区别。WHERE 子句用于处理”行”这种 0 阶的对象，而 HAVING 子句用来处理”集合”这种 1 阶的对象。</p>
<h2 id="总结"><a href="#总结" class="headerlink" title="总结"></a><strong>总结</strong></h2><p>1、SQL 严格区分层级，包括谓词逻辑中的层级（EXISTS），也包括集合论中的层级（GROUP BY）；</p>
<p>2、有了层级区分，那么适用于个体上的属性就不适用于团体了，这也就是为什么聚合查询的 SELECT 子句中不能直接引用原表中的列的原因；</p>
<p>3、一般来说，单元素集合的属性和其唯一元素的属性是一样的。这种只包含一个元素的集合让人觉得似乎没有必要特意地当成集合来看待，但是为了保持理论的完整性，我们还是要严格区分元素和单元素集合；</p>
 
      <!-- reward -->
      
    </div>
    

    <!-- copyright -->
    
    <footer class="article-footer">
       
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/" rel="tag">mysql</a></li></ul>

    </footer>
  </div>

    
 
   
</article>

    
    <article
  id="post-mysql/零散的MySQL基础总是记不住？看这一篇就够了"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h2 itemprop="name">
  <a class="article-title" href="/2020/11/11/mysql/%E9%9B%B6%E6%95%A3%E7%9A%84MySQL%E5%9F%BA%E7%A1%80%E6%80%BB%E6%98%AF%E8%AE%B0%E4%B8%8D%E4%BD%8F%EF%BC%9F%E7%9C%8B%E8%BF%99%E4%B8%80%E7%AF%87%E5%B0%B1%E5%A4%9F%E4%BA%86/"
    >零散的MySQL基础总是记不住？看这一篇就够了.md</a> 
</h2>
 

    </header>
     
    <div class="article-meta">
      <a href="/2020/11/11/mysql/%E9%9B%B6%E6%95%A3%E7%9A%84MySQL%E5%9F%BA%E7%A1%80%E6%80%BB%E6%98%AF%E8%AE%B0%E4%B8%8D%E4%BD%8F%EF%BC%9F%E7%9C%8B%E8%BF%99%E4%B8%80%E7%AF%87%E5%B0%B1%E5%A4%9F%E4%BA%86/" class="article-date">
  <time datetime="2020-11-10T16:00:00.000Z" itemprop="datePublished">2020-11-11</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/mysql/">mysql</a>
  </div>
   
    </div>
      
    <div class="article-entry" itemprop="articleBody">
       
  <h2 id="零散的MySQL基础总是记不住？看这一篇就够了！"><a href="#零散的MySQL基础总是记不住？看这一篇就够了！" class="headerlink" title="零散的MySQL基础总是记不住？看这一篇就够了！"></a>零散的MySQL基础总是记不住？看这一篇就够了！</h2><p>Sicimike <a href="javascript:void(0);">Java团长</a> <em>3天前</em></p>
<h2 id=""><a href="#" class="headerlink" title=""></a><img src="http://iubest.gitee.io/pic/640-1601002104254.webp" alt="img"></h2><h2 id="来源：http-suo-im-5Oa04O"><a href="#来源：http-suo-im-5Oa04O" class="headerlink" title="来源：http://suo.im/5Oa04O"></a>来源：<a target="_blank" rel="noopener" href="http://suo.im/5Oa04O">http://suo.im/5Oa04O</a></h2><h2 id="前言"><a href="#前言" class="headerlink" title="前言"></a>前言</h2><p>在日常开发中，一些不常用且又比较基础的知识，过了一段时间之后，总是容易忘记或者变得有点模棱两可。本篇主要记录一些关于MySQL数据库比较基础的知识，以便日后快速查看。</p>
<h2 id="SQL命令"><a href="#SQL命令" class="headerlink" title="SQL命令"></a>SQL命令</h2><p>SQL命令分可以分为四组：DDL、DML、DCL和TCL。四组中包含的命令分别如下<br><img src="http://iubest.gitee.io/pic/640-1601002104334.webp" alt="img"><br>（图片来源见水印）</p>
<h3 id="DDL"><a href="#DDL" class="headerlink" title="DDL"></a>DDL</h3><p>DDL是数据定义语言（Data Definition Language）的简称，它处理数据库schemas和描述数据应如何驻留在数据库中。</p>
<ul>
<li><code>CREATE</code>：创建数据库及其对象（如表，索引，视图，存储过程，函数和触发器）</li>
<li><code>ALTER</code>：改变现有数据库的结构</li>
<li><code>DROP</code>：从数据库中删除对象</li>
<li><code>TRUNCATE</code>：从表中删除所有记录，包括为记录分配的所有空间都将被删除</li>
<li><code>COMMENT</code>：添加注释</li>
<li><code>RENAME</code>：重命名对象</li>
</ul>
<p>常用命令如下：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br></pre></td><td class="code"><pre><span class="line"># 建表</span><br><span class="line">CREATE TABLE sicimike  (</span><br><span class="line">  id int(4) primary key auto_increment COMMENT &#39;主键ID&#39;,</span><br><span class="line">  name varchar(10) unique,</span><br><span class="line">  age int(3) default 0,</span><br><span class="line">  identity_card varchar(18)</span><br><span class="line">  # PRIMARY KEY (id) &#x2F;&#x2F; 也可以通过这种方式设置主键</span><br><span class="line">  # UNIQUE KEY (name) &#x2F;&#x2F; 也可以通过这种方式设置唯一键</span><br><span class="line">  # key&#x2F;index (identity_card, col1...) &#x2F;&#x2F; 也可以通过这种方式创建索引</span><br><span class="line">) ENGINE &#x3D; InnoDB;</span><br><span class="line"></span><br><span class="line"># 设置主键</span><br><span class="line">alter table sicimike add primary key(id);</span><br><span class="line"></span><br><span class="line"># 删除主键</span><br><span class="line">alter table sicimike drop primary key;</span><br><span class="line"></span><br><span class="line"># 设置唯一键</span><br><span class="line">alter table sicimike add unique key(column_name);</span><br><span class="line"></span><br><span class="line"># 删除唯一键</span><br><span class="line">alter table sicimike drop index column_name;</span><br><span class="line"></span><br><span class="line"># 创建索引</span><br><span class="line">alter table sicimike add [unique&#x2F;fulltext&#x2F;spatial] index&#x2F;key index_name (identity_card[(len)] [asc&#x2F;desc])[using btree&#x2F;hash]</span><br><span class="line">create [unique&#x2F;fulltext&#x2F;spatial] index index_name on sicimike(identity_card[(len)] [asc&#x2F;desc])[using btree&#x2F;hash]</span><br><span class="line">example： alter table sicimike add index idx_na(name, age);</span><br><span class="line"></span><br><span class="line"># 删除索引</span><br><span class="line">alter table sicimike drop key&#x2F;index identity_card;</span><br><span class="line">drop index index_name on sicimike;</span><br><span class="line"></span><br><span class="line"># 查看索引</span><br><span class="line">show index from sicimike;</span><br><span class="line"></span><br><span class="line"># 查看列</span><br><span class="line">desc sicimike;</span><br><span class="line"></span><br><span class="line"># 新增列</span><br><span class="line">alter table sicimike add column column_name varchar(30);</span><br><span class="line"></span><br><span class="line"># 删除列</span><br><span class="line">alter table sicimike drop column column_name;</span><br><span class="line"></span><br><span class="line"># 修改列名</span><br><span class="line">alter table sicimike change column_name new_name varchar(30);</span><br><span class="line"></span><br><span class="line"># 修改列属性</span><br><span class="line">alter table sicimike modify column_name varchar(22);</span><br><span class="line"></span><br><span class="line"># 查看建表信息</span><br><span class="line">show create table sicimike;</span><br><span class="line"></span><br><span class="line"># 添加表注释</span><br><span class="line">alter table sicimike comment &#39;表注释&#39;;</span><br><span class="line"></span><br><span class="line"># 添加字段注释</span><br><span class="line">alter table sicimike modify column column_name varchar(10) comment &#39;姓名&#39;;</span><br></pre></td></tr></table></figure>

<h3 id="DML"><a href="#DML" class="headerlink" title="DML"></a>DML</h3><p>DML是数据操纵语言（Data Manipulation Language）的简称，包括最常见的SQL语句，例如<code>SELECT</code>，<code>INSERT</code>，<code>UPDATE</code>，<code>DELETE</code>等，它用于存储，修改，检索和删除数据库中的数据。</p>
<p>分页</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">-- 查询从第11条数据开始的连续5条数据</span><br><span class="line">select * from sicimike limit 10, 5</span><br><span class="line">group by</span><br></pre></td></tr></table></figure>

<p>默认情况下，MySQL中的分组（<code>group by</code>）语句，不要求<code>select</code>返回的列，必须是分组的列或者是一个聚合函数。</p>
<p>如果<code>select</code>查询的列不是分组的列，也不是聚合函数，则会返回该分组中第一条记录的数据。对比下面两条SQL语句，第二条SQL语句中，<code>cname</code>既不是分组的列，也不是以聚合函数的形式出现。所以在<code>liming</code>这个分组中，<code>cname</code>取的是第一条数据。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from c;</span><br><span class="line">+-----+-------+----------+</span><br><span class="line">| CNO | CNAME | CTEACHER |</span><br><span class="line">+-----+-------+----------+</span><br><span class="line">|   1 | 数学  | liming   |</span><br><span class="line">|   2 | 语文  | liming   |</span><br><span class="line">|   3 | 历史  | xueyou   |</span><br><span class="line">|   4 | 物理  | guorong  |</span><br><span class="line">|   5 | 化学  | liming   |</span><br><span class="line">+-----+-------+----------+</span><br><span class="line">5 rows in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select cteacher, count(cteacher), cname from c group by cteacher;</span><br><span class="line">+----------+-----------------+-------+</span><br><span class="line">| cteacher | count(cteacher) | cname |</span><br><span class="line">+----------+-----------------+-------+</span><br><span class="line">| guorong  |               1 | 物理  |</span><br><span class="line">| liming   |               3 | 数学  |</span><br><span class="line">| xueyou   |               1 | 历史  |</span><br><span class="line">+----------+-----------------+-------+</span><br><span class="line">3 rows in set (0.00 sec)</span><br><span class="line">having</span><br></pre></td></tr></table></figure>

<p><code>having</code>关键字用于对分组后的数据进行筛选，功能相当于分组之前的<code>where</code>，不过要求更严格。过滤条件要么是一个聚合函数( <code>... having count(x) &gt; 1</code>)，要么是出现在select后面的列(<code>select col1, col2 ... group by x having col1 &gt; 1</code>)</p>
<p>多表更新</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">update tableA a inner join tableB b on a.xxx &#x3D; b.xxx set a.col1 &#x3D; xxx, b.col1 &#x3D; xxx where ...</span><br></pre></td></tr></table></figure>

<p>多表删除</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">delete a, b from tableA a inner join tableB b on a.xxx &#x3D; b.xxx where a.col1 &#x3D; xxx and b.col1 &#x3D; xxx</span><br></pre></td></tr></table></figure>

<h3 id="DCL"><a href="#DCL" class="headerlink" title="DCL"></a>DCL</h3><p>DCL是数据控制语言（Data Control Language）的简称，它包含诸如<code>GRANT</code>之类的命令，并且主要涉及数据库系统的权限，权限和其他控件。</p>
<ul>
<li><code>GRANT</code> ：允许用户访问数据库的权限</li>
<li><code>REVOKE</code>：撤消用户使用GRANT命令赋予的访问权限</li>
</ul>
<h3 id="TCL"><a href="#TCL" class="headerlink" title="TCL"></a>TCL</h3><p>TCL是事务控制语言（Transaction Control Language）的简称，用于处理数据库中的事务</p>
<ul>
<li><code>COMMIT</code>：提交事务</li>
<li><code>ROLLBACK</code>：在发生任何错误的情况下回滚事务</li>
</ul>
<h2 id="范式"><a href="#范式" class="headerlink" title="范式"></a>范式</h2><blockquote>
<p>数据库规范化，又称正规化、标准化，是数据库设计的一系列原理和技术，以减少数据库中数据冗余，增进数据的一致性。关系模型的发明者埃德加·科德最早提出这一概念，并于1970年代初定义了第一范式、第二范式和第三范式的概念，还与Raymond F. Boyce于1974年共同定义了第三范式的改进范式——BC范式。<br>除外还包括针对多值依赖的第四范式，连接依赖的第五范式、DK范式和第六范式。</p>
</blockquote>
<p>现在数据库设计最多满足3NF，普遍认为范式过高，虽然具有对数据关系更好的约束性，但也导致数据关系表增加而令数据库IO更易繁忙，原来交由数据库处理的关系约束现更多在数据库使用程序中完成。</p>
<h3 id="第一范式"><a href="#第一范式" class="headerlink" title="第一范式"></a>第一范式</h3><p>定义：数据库中的所有字段（列）都是单一属性，不可再分的。这个单一属性由基本的数据类型所构成，如整型、浮点型、字符串等。</p>
<p>第一范式是为了保证列的原子性。</p>
<p><img src="http://iubest.gitee.io/pic/640-1601002104430.webp" alt="img"></p>
<p>上表不满足第一范式，其中的地址列是可以再拆分的，可以拆分成省、市、区等</p>
<p><img src="http://iubest.gitee.io/pic/640-1601002104331.webp" alt="img"></p>
<h3 id="第二范式"><a href="#第二范式" class="headerlink" title="第二范式"></a>第二范式</h3><p>定义：数据库中的表不存在非关键字段对任一关键字字段的部分函数依赖</p>
<p>部分函数依赖是指存在着组合关键字中的某一关键字决定非关键字的情况</p>
<p>第二范式在满足了第一范式的基础上，消除非主键列对联合主键的部分依赖</p>
<p><img src="http://iubest.gitee.io/pic/640-1601002104402.webp" alt="img"></p>
<p>上面这张表中想要设置主键，只能是商品名称和供应商名称一起组成联合主键。但是价格和分类只依赖于商品名称，供应商电话只依赖于供应商名称，所以上面的表不满足第二范式，可以改成如下形式：</p>
<p>商品信息表</p>
<p><img src="http://iubest.gitee.io/pic/640-1601002104335.webp" alt="img"></p>
<p>供应商信息表</p>
<p><img src="http://iubest.gitee.io/pic/640-1601002104312.webp" alt="img"></p>
<p>商品-供应商关联表</p>
<p><img src="http://iubest.gitee.io/pic/640-1601002104428.webp" alt="img"></p>
<h3 id="第三范式"><a href="#第三范式" class="headerlink" title="第三范式"></a>第三范式</h3><p>定义：所有非主键属性都只和候选键有相关性，也就是说非主键属性之间应该是独立无关的。</p>
<p>第三范式是在满足了第二范式的基础上，消除列与列之间的传递依赖。</p>
<p><img src="http://iubest.gitee.io/pic/640-1601002104348.webp" alt="img"></p>
<p>在上面的表中，商品的分类描述依赖分类，而分类依赖商品名称，而不是分类描述直接依赖商品名称。这样就形成了传递依赖，所以不符合第三范式。可以改成如下形式</p>
<p>商品表</p>
<p><img src="http://iubest.gitee.io/pic/640-1601002104431.webp" alt="img"></p>
<p>商品分类表</p>
<p><img src="http://iubest.gitee.io/pic/640-1601002104311.webp" alt="img"></p>
<p>数据库设计时，遵循范式和反范式一直以来是一个颇受争议的问题。遵循范式对数据关系更好的约束性，并且减少数据冗余，可以更好地保证数据一致性。而反范式则是为了获得更好地性能。所以范式还是反范式并没有明确的标准，适合自己业务场景的才是最好的。</p>
<p>反范式设计时，需要考虑以下几个问题，分别是插入异常、更新异常和删除异常。</p>
<ul>
<li>插入异常：如果某个实体随着另一个实体的存在而存在，即缺少某个实体是无法表示这个实体，那么这个表就存在插入异常。</li>
<li>更新异常：如果更改表所对应的某个实体实例的单独属性时，需要将多行更新，那么就说明这个表存在更新异常</li>
<li>删除异常：如果删除表的某一行来表示某实体实例失效时，导致另一个不同实体实例信息丢失，那么这个表就存在删除异常</li>
</ul>
<p>以违反第二范式的表为例</p>
<p><img src="http://iubest.gitee.io/pic/640-1601002104427.webp" alt="img"></p>
<p>如果可乐第二制造厂这个供应商尚未开始供货，表中就不存在第二条记录，也就无法记录供应商的电话，这样就存在插入异常；如果需要把可乐的价格提高，需要更新表中的多条记录，这样就存在更新异常；如果删除可乐第二制造厂的供货信息，那么该供应商的电话也就丢失了，这样就存在删除异常。</p>
<p>一般存在插入异常的表，都会存在更新异常和删除异常。</p>
<h2 id="横表纵表"><a href="#横表纵表" class="headerlink" title="横表纵表"></a>横表纵表</h2><p>SQL脚本</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line"># 横表</span><br><span class="line">CREATE TABLE &#96;table_h2z&#96; (</span><br><span class="line">&#96;name&#96; varchar(32) DEFAULT NULL,</span><br><span class="line">&#96;chinese&#96; int(11) DEFAULT NULL,</span><br><span class="line">&#96;math&#96; int(11) DEFAULT NULL,</span><br><span class="line">&#96;english&#96; int(11) DEFAULT NULL</span><br><span class="line">) ENGINE&#x3D;InnoDB AUTO_INCREMENT&#x3D;4 DEFAULT CHARSET&#x3D;utf8;</span><br><span class="line"></span><br><span class="line">&#x2F;*Data for the table &#96;table_h2z&#96; *&#x2F;</span><br><span class="line">insert  into &#96;table_h2z&#96;(&#96;name&#96;,&#96;chinese&#96;,&#96;math&#96;,&#96;english&#96;) values </span><br><span class="line">(&#39;mike&#39;,45,43,87),</span><br><span class="line">(&#39;lily&#39;,53,64,88),</span><br><span class="line">(&#39;lucy&#39;,57,75,75);</span><br><span class="line"></span><br><span class="line"># 纵表</span><br><span class="line">CREATE TABLE &#96;table_z2h&#96; (</span><br><span class="line">  &#96;name&#96; varchar(32) DEFAULT NULL,</span><br><span class="line">  &#96;subject&#96; varchar(8) NOT NULL DEFAULT &#39;&#39;,</span><br><span class="line">  &#96;score&#96; int(11) DEFAULT NULL</span><br><span class="line">) ENGINE&#x3D;InnoDB DEFAULT CHARSET&#x3D;utf8;</span><br><span class="line"></span><br><span class="line">&#x2F;*Data for the table &#96;table_z2h&#96; *&#x2F;</span><br><span class="line">insert  into &#96;table_z2h&#96;(&#96;name&#96;,&#96;subject&#96;,&#96;score&#96;) values </span><br><span class="line">(&#39;mike&#39;,&#39;chinese&#39;,45),</span><br><span class="line">(&#39;lily&#39;,&#39;chinese&#39;,53),</span><br><span class="line">(&#39;lucy&#39;,&#39;chinese&#39;,57),</span><br><span class="line">(&#39;mike&#39;,&#39;math&#39;,43),</span><br><span class="line">(&#39;lily&#39;,&#39;math&#39;,64),</span><br><span class="line">(&#39;lucy&#39;,&#39;math&#39;,75),</span><br><span class="line">(&#39;mike&#39;,&#39;english&#39;,87),</span><br><span class="line">(&#39;lily&#39;,&#39;english&#39;,88),</span><br><span class="line">(&#39;lucy&#39;,&#39;english&#39;,75);</span><br></pre></td></tr></table></figure>

<h3 id="横表转纵表"><a href="#横表转纵表" class="headerlink" title="横表转纵表"></a>横表转纵表</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SELECT NAME, &#39;chinese&#39; AS &#96;subject&#96;,  chinese AS &#96;score&#96; FROM table_h2z</span><br><span class="line">UNION ALL</span><br><span class="line">SELECT NAME, &#39;math&#39; AS &#96;subject&#96;,  math AS &#96;score&#96; FROM table_h2z</span><br><span class="line">UNION ALL</span><br><span class="line">SELECT NAME, &#39;english&#39; AS &#96;subject&#96;, english AS &#96;score&#96; FROM table_h2z</span><br></pre></td></tr></table></figure>

<p>执行结果</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">+------+---------+-------+</span><br><span class="line">| name | subject | score |</span><br><span class="line">+------+---------+-------+</span><br><span class="line">| mike | chinese |    45 |</span><br><span class="line">| lily | chinese |    53 |</span><br><span class="line">| lucy | chinese |    57 |</span><br><span class="line">| mike | math    |    43 |</span><br><span class="line">| lily | math    |    64 |</span><br><span class="line">| lucy | math    |    75 |</span><br><span class="line">| mike | english |    87 |</span><br><span class="line">| lily | english |    88 |</span><br><span class="line">| lucy | english |    75 |</span><br><span class="line">+------+---------+-------+</span><br><span class="line">9 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>

<h3 id="纵表转横表"><a href="#纵表转横表" class="headerlink" title="纵表转横表"></a>纵表转横表</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">SELECT NAME,</span><br><span class="line"> SUM(CASE &#96;subject&#96; WHEN &#39;chinese&#39; THEN score ELSE 0 END) AS chinese,</span><br><span class="line"> SUM(CASE &#96;subject&#96; WHEN &#39;math&#39; THEN score ELSE 0 END) AS math,</span><br><span class="line"> SUM(CASE &#96;subject&#96; WHEN &#39;english&#39; THEN score ELSE 0 END) AS english</span><br><span class="line">FROM table_z2h</span><br><span class="line">GROUP BY NAME</span><br></pre></td></tr></table></figure>

<p>执行结果</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">+------+---------+------+---------+</span><br><span class="line">| name | chinese | math | english |</span><br><span class="line">+------+---------+------+---------+</span><br><span class="line">| lily |      53 |   64 |      88 |</span><br><span class="line">| lucy |      57 |   75 |      75 |</span><br><span class="line">| mike |      45 |   43 |      87 |</span><br><span class="line">+------+---------+------+---------+</span><br><span class="line">3 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>

<h2 id="参考"><a href="#参考" class="headerlink" title="参考"></a>参考</h2><ul>
<li><a target="_blank" rel="noopener" href="https://www.w3schools.in/mysql/ddl-dml-dcl/">https://www.w3schools.in/mysql/ddl-dml-dcl/</a></li>
</ul>
<p><strong>END</strong></p>
 
      <!-- reward -->
      
    </div>
    

    <!-- copyright -->
    
    <footer class="article-footer">
       
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/" rel="tag">mysql</a></li></ul>

    </footer>
  </div>

    
 
   
</article>

    
    <article
  id="post-oa/Python实现数据写入 Excel 的三种模块"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h2 itemprop="name">
  <a class="article-title" href="/2020/11/11/oa/Python%E5%AE%9E%E7%8E%B0%E6%95%B0%E6%8D%AE%E5%86%99%E5%85%A5%20Excel%20%E7%9A%84%E4%B8%89%E7%A7%8D%E6%A8%A1%E5%9D%97/"
    >Python实现数据写入 Excel 的三种模块.md</a> 
</h2>
 

    </header>
     
    <div class="article-meta">
      <a href="/2020/11/11/oa/Python%E5%AE%9E%E7%8E%B0%E6%95%B0%E6%8D%AE%E5%86%99%E5%85%A5%20Excel%20%E7%9A%84%E4%B8%89%E7%A7%8D%E6%A8%A1%E5%9D%97/" class="article-date">
  <time datetime="2020-11-10T16:00:00.000Z" itemprop="datePublished">2020-11-11</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/oa/">oa</a>
  </div>
   
    </div>
      
    <div class="article-entry" itemprop="articleBody">
       
  <h2 id="Python实现数据写入-Excel-的三种模块！"><a href="#Python实现数据写入-Excel-的三种模块！" class="headerlink" title="Python实现数据写入 Excel 的三种模块！"></a>Python实现数据写入 Excel 的三种模块！</h2><p>原创 Ryoko <a href="javascript:void(0);">凹凸数据</a> <em>昨天</em></p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/tXYict40xfLhWKJh3biaotT45JWqYibJ1wUp3UQMzpYNaUBrcgQ3TTGiaXfv75m7GJLnwopYbRiaRqmFTAZa76ia9KTQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="img"></p>
<p>作者：Ryoko</p>
<p>源自：凹凸数据</p>
<p>本文说明如何使用 <code>xlsxwriter</code>、<code>pandas</code>、<code>openpyxl</code> 三个库来实现数据写入 Excel，写入后的格式如下图所示：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz/tXYict40xfLia2jXhFO0xBeBg3VfZUCJibTbarf0vAibuDXzkmdX1f0EvF0AbCWXsWJnVvbVVVyZPHcuV39gJ1srGw/640?wx_fmt=other&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="img"></p>
<p>以下为数据用例：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">import xlsxwriter as xw</span><br><span class="line">import pandas as pd</span><br><span class="line">import openpyxl as op</span><br><span class="line"></span><br><span class="line">&quot;-------------数据用例-------------&quot;</span><br><span class="line">orderIds &#x3D; [1,2,3]</span><br><span class="line">items &#x3D; [&#39;A&#39;,&#39;B&#39;,&#39;C&#39;]</span><br><span class="line">myData &#x3D; [10,20,30]</span><br><span class="line">testData &#x3D; [orderIds,items,myData]</span><br><span class="line">filename1 &#x3D; &#39;测试1.xlsx&#39;</span><br><span class="line">filename2 &#x3D; &#39;测试2.xlsx&#39;</span><br><span class="line">filename3 &#x3D; &#39;测试3.xlsx&#39;</span><br><span class="line"></span><br><span class="line">&quot;--------------执行----------------&quot;</span><br><span class="line">xw_toexcel(testData,filename1)</span><br><span class="line">pd_toexcel(testData,filename2)</span><br><span class="line">op_toexcel(testData,filename3)</span><br></pre></td></tr></table></figure>

<p>下面开始分别介绍三种方法：</p>
<h2 id="1、xlsxwriter"><a href="#1、xlsxwriter" class="headerlink" title="1、xlsxwriter"></a>1、xlsxwriter</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">def xw_toexcel(data,filename): # xlsxwriter库储存数据到excel</span><br><span class="line">    workbook &#x3D; xw.Workbook(filename) # 创建工作簿</span><br><span class="line">    worksheet1 &#x3D; workbook.add_worksheet(&quot;sheet1&quot;) # 创建子表</span><br><span class="line">    worksheet1.activate() # 激活表</span><br><span class="line">    title &#x3D; [&#39;序号&#39;,&#39;项目&#39;,&#39;数据&#39;] # 设置表头</span><br><span class="line">    worksheet1.write_row(&#39;A1&#39;,title) # 从A1单元格开始写入表头</span><br><span class="line">    i &#x3D; 2 # 从第二行开始写入数据</span><br><span class="line">    for j in range(len(data)):</span><br><span class="line">        insertData &#x3D; [data[0][j],data[1][j],data[2][j]]</span><br><span class="line">        row &#x3D; &#39;A&#39; + str(i)</span><br><span class="line">        worksheet1.write_row(row, insertData)</span><br><span class="line">        i +&#x3D; 1</span><br><span class="line">    workbook.close() # 关闭表</span><br></pre></td></tr></table></figure>

<p><img src="https://mmbiz.qpic.cn/mmbiz_png/tXYict40xfLia2jXhFO0xBeBg3VfZUCJibTb2K3Wb2S9s0aERuqOQOqzVUjGV3iahThDWLnicOhldjyRP41c2YJZwKw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="img"></p>
<h2 id="2、pandas"><a href="#2、pandas" class="headerlink" title="2、pandas"></a>2、pandas</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">def pd_toexcel(data,filename): # pandas库储存数据到excel</span><br><span class="line">    dfData &#x3D; &#123; # 用字典设置DataFrame所需数据</span><br><span class="line">        &#39;序号&#39;:data[0],</span><br><span class="line">        &#39;项目&#39;:data[1],</span><br><span class="line">        &#39;数据&#39;:data[2]</span><br><span class="line">    &#125;</span><br><span class="line">    df &#x3D; pd.DataFrame(dfData) # 创建DataFrame</span><br><span class="line">    df.to_excel(filename,index&#x3D;False) # 存表，去除原始索引列（0,1,2...）</span><br></pre></td></tr></table></figure>

<p><img src="https://mmbiz.qpic.cn/mmbiz_png/tXYict40xfLia2jXhFO0xBeBg3VfZUCJibTc1nxk5gu6wSvG2CQtqibnEJdSqegNstibfCNaDb28Tuic5zGj7MKgGMFw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="img"></p>
<h2 id="3、openpyxl"><a href="#3、openpyxl" class="headerlink" title="3、openpyxl"></a>3、openpyxl</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">def op_toexcel(data,filename): # openpyxl库储存数据到excel</span><br><span class="line">    wb &#x3D; op.Workbook() # 创建工作簿对象</span><br><span class="line">    ws &#x3D; wb[&#39;Sheet&#39;] # 创建子表</span><br><span class="line">    ws.append([&#39;序号&#39;,&#39;项目&#39;,&#39;数据&#39;]) # 添加表头</span><br><span class="line">    for i in range(len(data[0])):</span><br><span class="line">        d &#x3D; data[0][i], data[1][i], data[2][i]</span><br><span class="line">        ws.append(d) # 每次写入一行</span><br><span class="line">    wb.save(filename)</span><br></pre></td></tr></table></figure>

<p><img src="https://mmbiz.qpic.cn/mmbiz_png/tXYict40xfLia2jXhFO0xBeBg3VfZUCJibTBGRT4wrWCdMaThFj295c6Lwb8kDERYFg9C6KXo0zlMJU279dIWXib7A/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="img"></p>
<h3 id="小结"><a href="#小结" class="headerlink" title="小结"></a>小结</h3><p>openpyxl 与 xlsxwriter 类似，需要逐行写入，如第一行需要得到 [ 1, ‘A’ , 10 ] 的格式，但不同的是使用 <code>openpyxl.load_workbook(filepath)</code> 可以对已存在的 .xlsx 进行追加数据，不会覆盖；pandas 则需先按列组合，如 [[ 1,2,3],[‘A’,’B’,’C’],[…]]的格式。</p>
<p>方法各有好处，写表时应根据需要选用。</p>
<h3 id="本文完整源码下载："><a href="#本文完整源码下载：" class="headerlink" title="本文完整源码下载："></a>本文完整源码下载：</h3><p><a target="_blank" rel="noopener" href="https://alltodata.cowtransfer.com/s/067cecabd5004b">https://alltodata.cowtransfer.com/s/067cecabd5004b</a></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br></pre></td><td class="code"><pre><span class="line"># -*- coding: utf-8 -*-</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">Created on Sun Oct  4 21:56:37 2020</span><br><span class="line"></span><br><span class="line">@author: 朱小五</span><br><span class="line"></span><br><span class="line">微信公众号: 凹凸数据</span><br><span class="line"></span><br><span class="line">公众号ID: alltodata</span><br><span class="line">&quot;&quot;&quot;</span><br><span class="line">import xlsxwriter as xw</span><br><span class="line">import pandas as pd</span><br><span class="line">import openpyxl as op</span><br><span class="line"></span><br><span class="line">&quot;-------------数据用例-------------&quot;</span><br><span class="line">orderIds &#x3D; [1,2,3]</span><br><span class="line">items &#x3D; [&#39;A&#39;,&#39;B&#39;,&#39;C&#39;]</span><br><span class="line">myData &#x3D; [10,20,30]</span><br><span class="line">testData &#x3D; [orderIds,items,myData]</span><br><span class="line">filename1 &#x3D; &#39;测试1.xlsx&#39;</span><br><span class="line">filename2 &#x3D; &#39;测试2.xlsx&#39;</span><br><span class="line">filename3 &#x3D; &#39;测试3.xlsx&#39;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">def xw_toexcel(data,filename): # xlsxwriter库储存数据到excel</span><br><span class="line">    workbook &#x3D; xw.Workbook(filename) # 创建工作簿</span><br><span class="line">    worksheet1 &#x3D; workbook.add_worksheet(&quot;sheet1&quot;) # 创建子表</span><br><span class="line">    worksheet1.activate() # 激活表</span><br><span class="line">    title &#x3D; [&#39;序号&#39;,&#39;项目&#39;,&#39;数据&#39;] # 设置表头</span><br><span class="line">    worksheet1.write_row(&#39;A1&#39;,title) # 从A1单元格开始写入表头</span><br><span class="line">    i &#x3D; 2 # 从第二行开始写入数据</span><br><span class="line">    for j in range(len(data)):</span><br><span class="line">        insertData &#x3D; [data[0][j],data[1][j],data[2][j]]</span><br><span class="line">        row &#x3D; &#39;A&#39; + str(i)</span><br><span class="line">        worksheet1.write_row(row, insertData)</span><br><span class="line">        i +&#x3D; 1</span><br><span class="line">    workbook.close() # 关闭表</span><br><span class="line">    </span><br><span class="line">    </span><br><span class="line">def pd_toexcel(data,filename): # pandas库储存数据到excel</span><br><span class="line">    dfData &#x3D; &#123; # 用字典设置DataFrame所需数据</span><br><span class="line">        &#39;序号&#39;:data[0],</span><br><span class="line">        &#39;项目&#39;:data[1],</span><br><span class="line">        &#39;数据&#39;:data[2]</span><br><span class="line">    &#125;</span><br><span class="line">    df &#x3D; pd.DataFrame(dfData) # 创建DataFrame</span><br><span class="line">    df.to_excel(filename,index&#x3D;False) # 存表，去除原始索引列（0,1,2...）</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">def op_toexcel(data,filename): # openpyxl库储存数据到excel</span><br><span class="line">    wb &#x3D; op.Workbook() # 创建工作簿对象</span><br><span class="line">    ws &#x3D; wb[&#39;Sheet&#39;] # 创建子表</span><br><span class="line">    ws.append([&#39;序号&#39;,&#39;项目&#39;,&#39;数据&#39;]) # 添加表头</span><br><span class="line">    for i in range(len(data[0])):</span><br><span class="line">        d &#x3D; data[0][i], data[1][i], data[2][i]</span><br><span class="line">        ws.append(d) # 每次写入一行</span><br><span class="line">    wb.save(filename)</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">&quot;--------------执行----------------&quot;</span><br><span class="line">xw_toexcel(testData,filename1)</span><br><span class="line">pd_toexcel(testData,filename2)</span><br><span class="line">op_toexcel(testData,filename3)</span><br><span class="line"></span><br></pre></td></tr></table></figure>

 
      <!-- reward -->
      
    </div>
    

    <!-- copyright -->
    
    <footer class="article-footer">
       
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/oa/" rel="tag">oa</a></li></ul>

    </footer>
  </div>

    
 
   
</article>

    
    <article
  id="post-oa/详解Python操作Excel神器openpyxl的各种操作"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h2 itemprop="name">
  <a class="article-title" href="/2020/11/11/oa/%E8%AF%A6%E8%A7%A3Python%E6%93%8D%E4%BD%9CExcel%E7%A5%9E%E5%99%A8openpyxl%E7%9A%84%E5%90%84%E7%A7%8D%E6%93%8D%E4%BD%9C/"
    >详解Python操作Excel神器openpyxl的各种操作.md</a> 
</h2>
 

    </header>
     
    <div class="article-meta">
      <a href="/2020/11/11/oa/%E8%AF%A6%E8%A7%A3Python%E6%93%8D%E4%BD%9CExcel%E7%A5%9E%E5%99%A8openpyxl%E7%9A%84%E5%90%84%E7%A7%8D%E6%93%8D%E4%BD%9C/" class="article-date">
  <time datetime="2020-11-10T16:00:00.000Z" itemprop="datePublished">2020-11-11</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/oa/">oa</a>
  </div>
   
    </div>
      
    <div class="article-entry" itemprop="articleBody">
       
  <h1 id="详解Python操作Excel神器openpyxl的各种操作"><a href="#详解Python操作Excel神器openpyxl的各种操作" class="headerlink" title="详解Python操作Excel神器openpyxl的各种操作"></a>详解Python操作Excel神器openpyxl的各种操作</h1><p>大家好，在之前的十几篇办公自动化系列文章中，我们大多是以<strong>真实的案例需求</strong>来讲解Python如何进行自动化办公操作，并且多次使用到<code>openpyxl</code>来处理表格，今天我们就来详细的<strong>盘点Python操作Excel神器openpyxl的各种操作！</strong></p>
<blockquote>
<p>本文将以详细图表/代码的形式讲解如何对Excel进行<strong>读取</strong>、<strong>写入</strong>及<strong>样式调整</strong>，可以当成<strong>速查手册</strong>使用，随用随查，建议收藏！</p>
</blockquote>
<h2 id="安装"><a href="#安装" class="headerlink" title="安装"></a>安装</h2><p><code>openpyxl</code>是一个非标准库，因此需要自行安装，安装过程并不困难，<strong>Windows/Mac</strong>用户均可以在命令行(CMD)/终端(Terminal)中使用pip安装</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">pip install openpyxl</span><br></pre></td></tr></table></figure>

<h2 id="前置知识"><a href="#前置知识" class="headerlink" title="前置知识"></a>前置知识</h2><p>在使用这个模块之前我们需要对Excel表格结构有个比较细致的了解，见下图：<img src="http://iubest.gitee.io/pic/640-1601168112420.png" alt="img">简而言之，一个Excel工作簿<code>workbook</code>由一个或者多个工作表<code>sheet</code>组成，一个sheet可以看作是多个行<code>row</code>组成，也可以看作是多个列<code>column</code>组成，而每一行每一列都由多个单元格<code>cell</code>组成！</p>
<h2 id="读取Excel"><a href="#读取Excel" class="headerlink" title="读取Excel"></a>读取Excel</h2><h3 id="1-载入Excel"><a href="#1-载入Excel" class="headerlink" title="1.载入Excel"></a>1.载入Excel</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">from openpyxl import load_workbook</span><br><span class="line">workbook &#x3D; load_workbook(filename&#x3D;&#39;测试.xlsx’) </span><br><span class="line">print(workbook.sheetnames)</span><br></pre></td></tr></table></figure>

<p>注意<code>load_workbook</code>只能打开已经存在的Excel，不能创建新的工作簿</p>
<h3 id="2-根据名称获取工作表"><a href="#2-根据名称获取工作表" class="headerlink" title="2.根据名称获取工作表"></a>2.根据名称获取工作表</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">from openpyxl import load_workbook</span><br><span class="line">workbook &#x3D; load_workbook(filename&#x3D;&#39;其他.xlsx&#39;) </span><br><span class="line">print(workbook.sheetnames)</span><br><span class="line">sheet &#x3D; workbook[&#39;工作业务&#39;]</span><br></pre></td></tr></table></figure>

<p>如果只有一张工作表也可以用：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">sheet &#x3D; workbook.active</span><br></pre></td></tr></table></figure>

<h3 id="3-获取表格内容所在的范围"><a href="#3-获取表格内容所在的范围" class="headerlink" title="3.获取表格内容所在的范围"></a>3.获取表格内容所在的范围</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">print(sheet.dimensions)</span><br></pre></td></tr></table></figure>

<h3 id="4-获取某个单元格的具体内容"><a href="#4-获取某个单元格的具体内容" class="headerlink" title="4.获取某个单元格的具体内容"></a>4.获取某个单元格的具体内容</h3><p>这边提供两种方法，注意都需要以<code>cell.value</code>形式输出具体值<img src="http://iubest.gitee.io/pic/640-1601168149291.png" alt="img"></p>
<h3 id="5-获取某个单元格的行、列、坐标"><a href="#5-获取某个单元格的行、列、坐标" class="headerlink" title="5.获取某个单元格的行、列、坐标"></a>5.获取某个单元格的行、列、坐标</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">print(cell.row, cell.column, cell.coordinate)</span><br></pre></td></tr></table></figure>

<h3 id="6-获取多个格子的值"><a href="#6-获取多个格子的值" class="headerlink" title="6.获取多个格子的值"></a>6.获取多个格子的值</h3><p><img src="http://iubest.gitee.io/pic/640-1601168112421.webp" alt="img">这里也有一个细节，Excel中每一列由字母确定，是字符型；每一行由一个数字确定，是<strong>整型</strong>。当然，上面的三种方法都是获取一堆表格，现在要输出每一个表格的值就需要<strong>遍历</strong>：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">for cell in cells: </span><br><span class="line">    print(cell.value)</span><br></pre></td></tr></table></figure>

<p>三种方法依然有自己的<strong>局限性</strong>，如果我需要特定范围的值，且懒得换算成字母数字坐标。例如我想要获取第2行至第5行、第1列至第3列的全部单元格。因此必须掌握第4种方法：</p>
<p><img src="http://iubest.gitee.io/pic/640-1601168112426.png" alt="img"></p>
<h3 id="7-读取所有的行"><a href="#7-读取所有的行" class="headerlink" title="7.读取所有的行"></a>7.读取所有的行</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">for row in sheet.rows: </span><br><span class="line">    print(row)</span><br></pre></td></tr></table></figure>

<h2 id="Excel写入"><a href="#Excel写入" class="headerlink" title="Excel写入"></a>Excel写入</h2><h3 id="1-保存Excel"><a href="#1-保存Excel" class="headerlink" title="1. 保存Excel"></a>1. 保存Excel</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">workbook.save(filename&#x3D;&#39;Excel工作表1.xlsx&#39;)</span><br></pre></td></tr></table></figure>

<ul>
<li>如果读取和写入Excel的路径相同则为对原文件进行<strong>修改</strong>，</li>
<li>如果读取和写入Excel的路径不同则为<strong>保存成新的文件</strong></li>
</ul>
<h3 id="2-写入单元格"><a href="#2-写入单元格" class="headerlink" title="2.写入单元格"></a>2.写入单元格</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">cell &#x3D; sheet[&#39;A1&#39;] </span><br><span class="line">cell.value &#x3D; &#39;业务需求</span><br></pre></td></tr></table></figure>

<h3 id="3-写入一行或多行数据"><a href="#3-写入一行或多行数据" class="headerlink" title="3.写入一行或多行数据"></a>3.写入一行或多行数据</h3><p><img src="http://iubest.gitee.io/pic/640-1601168112419.png" alt="img"><img src="http://iubest.gitee.io/pic/640-1601168112424.png" alt="img"></p>
<h3 id="4-将公式写入单元格并保存"><a href="#4-将公式写入单元格并保存" class="headerlink" title="4.将公式写入单元格并保存"></a>4.将公式写入单元格并保存</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">sheet[&#39;K11&#39;] &#x3D; &#39;&#x3D;AVERAGE(K1:K10)&#39; </span><br></pre></td></tr></table></figure>

<h3 id="5-插入一行或多行"><a href="#5-插入一行或多行" class="headerlink" title="5.插入一行或多行"></a>5.插入一行或多行</h3><p><img src="http://iubest.gitee.io/pic/640-1601168112427.png" alt="img"></p>
<h3 id="6-插入一列或多列"><a href="#6-插入一列或多列" class="headerlink" title="6. 插入一列或多列"></a>6. 插入一列或多列</h3><p><img src="http://iubest.gitee.io/pic/640-1601168112426.png" alt="img"></p>
<h3 id="7-删除多行"><a href="#7-删除多行" class="headerlink" title="7.删除多行"></a>7.删除多行</h3><p><img src="http://iubest.gitee.io/pic/640-1601168112423.webp" alt="img"></p>
<h3 id="8-删除多列"><a href="#8-删除多列" class="headerlink" title="8.删除多列"></a>8.删除多列</h3><p><img src="http://iubest.gitee.io/pic/640-1601168112425.png" alt="img"></p>
<h3 id="9-移动范围数据"><a href="#9-移动范围数据" class="headerlink" title="9. 移动范围数据"></a>9. 移动范围数据</h3><p><img src="http://iubest.gitee.io/pic/640-1601168112421.png" alt="img"></p>
<h3 id="10-创建新的Excel表格"><a href="#10-创建新的Excel表格" class="headerlink" title="10. 创建新的Excel表格"></a>10. 创建新的Excel表格</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">from openpyxl import Workbook</span><br><span class="line">workbook &#x3D; Workbook()</span><br></pre></td></tr></table></figure>

<h2 id="Excel样式调整"><a href="#Excel样式调整" class="headerlink" title="Excel样式调整"></a>Excel样式调整</h2><h3 id="1-设置字体样式"><a href="#1-设置字体样式" class="headerlink" title="1. 设置字体样式"></a>1. 设置字体样式</h3><p><img src="http://iubest.gitee.io/pic/640-1601168167823.png" alt="img"></p>
<h3 id="2-设置对齐样式"><a href="#2-设置对齐样式" class="headerlink" title="2. 设置对齐样式"></a>2. 设置对齐样式</h3><p><img src="http://iubest.gitee.io/pic/640-1601168112423.png" alt="img"></p>
<ul>
<li>水平对齐:<code>distributed, justify, center, left, fill, centerContinuous, right, general</code></li>
<li>垂直对齐:<code>bottom, distributed, justify, center, top</code></li>
</ul>
<h3 id="3-设置边框样式"><a href="#3-设置边框样式" class="headerlink" title="3. 设置边框样式"></a>3. 设置边框样式</h3><p><img src="http://iubest.gitee.io/pic/640-1601168112424.png" alt="img"></p>
<ul>
<li>边线样式:<code>double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick</code></li>
</ul>
<h3 id="4-设置单元格填充样式"><a href="#4-设置单元格填充样式" class="headerlink" title="4. 设置单元格填充样式"></a>4. 设置单元格填充样式</h3><p><img src="http://iubest.gitee.io/pic/640-1601168112422.png" alt="img"></p>
<h3 id="5-设置行高和列宽"><a href="#5-设置行高和列宽" class="headerlink" title="5. 设置行高和列宽"></a>5. 设置行高和列宽</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">sheet.row_dimensions[1].height &#x3D; 50 </span><br><span class="line">sheet.column_dimensions[&#39;C&#39;].width &#x3D; 20 </span><br></pre></td></tr></table></figure>

<h3 id="6-单元格合并与取消"><a href="#6-单元格合并与取消" class="headerlink" title="6. 单元格合并与取消"></a>6. 单元格合并与取消</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"># 合并</span><br><span class="line">sheet.merge_cells(&#39;A1:B2&#39;) </span><br><span class="line">sheet.merge_cells(start_row&#x3D;1, start_column&#x3D;3, </span><br><span class="line">                  end_row&#x3D;2, end_column&#x3D;4)</span><br><span class="line"></span><br><span class="line"># 取消合并</span><br><span class="line">sheet.unmerge_cells(&#39;A1:B2&#39;) </span><br><span class="line">sheet.unmerge_cells(start_row&#x3D;1, start_column&#x3D;3, </span><br><span class="line">                    end_row&#x3D;2, end_column&#x3D;4)</span><br></pre></td></tr></table></figure>

<hr>
<p>有了上面的基础知识之后我们就能更快速的与办公自动化需求结合使用，附几篇往期涉及openpyxl自动化相关文章大家体会一下👇(点击查看)</p>
<p><a target="_blank" rel="noopener" href="https://mp.weixin.qq.com/s?__biz=MzI1MTUyMjc1Mg==&mid=2247497825&idx=1&sn=7cc7da0ffe1ad480881c5ac25d7f545e&scene=21#wechat_redirect">1. Python+Excel+Word一秒制作百份合同</a></p>
<p><a target="_blank" rel="noopener" href="https://mp.weixin.qq.com/s?__biz=MzI1MTUyMjc1Mg==&mid=2247497058&idx=1&sn=e96efd9a264e5e9e420016597678589b&scene=21#wechat_redirect">2. 老板让我从几百个Excel中查找数据，我用Python一分钟搞定！</a></p>
<p><a target="_blank" rel="noopener" href="https://mp.weixin.qq.com/s?__biz=MzI1MTUyMjc1Mg==&mid=2247495588&idx=1&sn=66051926b3465ba0ab874377a1fd13bc&scene=21#wechat_redirect">3. Python办公自动化｜批量提取Excel数据</a></p>
 
      <!-- reward -->
      
    </div>
    

    <!-- copyright -->
    
    <footer class="article-footer">
       
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/oa/" rel="tag">oa</a></li></ul>

    </footer>
  </div>

    
 
   
</article>

    
    <article
  id="post-deploy/Centos7升级sqlite3"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h2 itemprop="name">
  <a class="article-title" href="/2020/11/11/deploy/Centos7%E5%8D%87%E7%BA%A7sqlite3/"
    >Centos7升级sqlite3.md</a> 
</h2>
 

    </header>
     
    <div class="article-meta">
      <a href="/2020/11/11/deploy/Centos7%E5%8D%87%E7%BA%A7sqlite3/" class="article-date">
  <time datetime="2020-11-10T16:00:00.000Z" itemprop="datePublished">2020-11-11</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/deploy/">deploy</a>
  </div>
   
    </div>
      
    <div class="article-entry" itemprop="articleBody">
       
  <p>Centos7升级sqlite3</p>
<p>1.下载源码</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">#</span><span class="bash">wget https://www.sqlite.org/2020/sqlite-autoconf-3310100.tar.gz</span></span><br><span class="line">wget https://share-1300858685.cos.ap-shanghai.myqcloud.com/share/sqlite-autoconf-3310100.tar.gz</span><br></pre></td></tr></table></figure>
<p>2.解压，编译</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">tar zxvf sqlite-autoconf-3310100.tar.gz -C /usr/src</span><br><span class="line">cd /usr/src/sqlite-autoconf-3310100/</span><br><span class="line">./configure --prefix=/usr/local</span><br><span class="line">make &amp;&amp; make install</span><br></pre></td></tr></table></figure>


<p>3.替换系统低版本sqlite3</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">mv /usr/bin/sqlite3  /usr/bin/sqlite3_old</span><br><span class="line">ln -s /usr/local/bin/sqlite3   /usr/bin/sqlite3</span><br><span class="line">echo &quot;/usr/local/lib&quot; &gt; /etc/ld.so.conf.d/sqlite3.conf</span><br><span class="line">ldconfig</span><br><span class="line">sqlite3 -version</span><br></pre></td></tr></table></figure> 
      <!-- reward -->
      
    </div>
    

    <!-- copyright -->
    
    <footer class="article-footer">
       
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/deploy/" rel="tag">deploy</a></li></ul>

    </footer>
  </div>

    
 
   
</article>

    
    <article
  id="post-other/2020 年开发者生态报告：Python超越Java，Go、Kotlin强势崛起"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h2 itemprop="name">
  <a class="article-title" href="/2020/11/11/other/2020%20%E5%B9%B4%E5%BC%80%E5%8F%91%E8%80%85%E7%94%9F%E6%80%81%E6%8A%A5%E5%91%8A%EF%BC%9APython%E8%B6%85%E8%B6%8AJava%EF%BC%8CGo%E3%80%81Kotlin%E5%BC%BA%E5%8A%BF%E5%B4%9B%E8%B5%B7/"
    >2020 年开发者生态报告：Python超越Java，Go、Kotlin强势崛起.md</a> 
</h2>
 

    </header>
     
    <div class="article-meta">
      <a href="/2020/11/11/other/2020%20%E5%B9%B4%E5%BC%80%E5%8F%91%E8%80%85%E7%94%9F%E6%80%81%E6%8A%A5%E5%91%8A%EF%BC%9APython%E8%B6%85%E8%B6%8AJava%EF%BC%8CGo%E3%80%81Kotlin%E5%BC%BA%E5%8A%BF%E5%B4%9B%E8%B5%B7/" class="article-date">
  <time datetime="2020-11-10T16:00:00.000Z" itemprop="datePublished">2020-11-11</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/other/">other</a>
  </div>
   
    </div>
      
    <div class="article-entry" itemprop="articleBody">
       
  <h1 id="2020-年开发者生态报告：Python超越Java，Go、Kotlin强势崛起"><a href="#2020-年开发者生态报告：Python超越Java，Go、Kotlin强势崛起" class="headerlink" title="2020 年开发者生态报告：Python超越Java，Go、Kotlin强势崛起"></a>2020 年开发者生态报告：Python超越Java，Go、Kotlin强势崛起</h1><p><img src="http://iubest.gitee.io/pic/640-1601007651451.webp" alt="img"></p>
<p>来源：JetBrains</p>
<p>编辑：白峰、鹏飞、舒婷</p>
<p>本文转自：新智元</p>
<p>近日，JetBrains发布了一份<strong>2020年开发者生态报告</strong>。该报告是基于19696个开发者的反馈所得到的，目标是完成对2020年开发环境，开发工具，开发语言的趋势调研。这是JetBrains第四年发布开发者生态报告，相比参与人数差不多是2017年的4倍！</p>
<p>JetBrains就是一家不折不扣的「<strong>别人家的公司</strong>」。做IDE起家，成立之初仅仅3个人，20年时间已经成长为一家近千人的大厂。难得的是，20年来从来没进行过融资，纯靠软件的营收！网传每位员工收入约 35 万美元；2018 年自由现金流（FCF）高达 50%。</p>
<p>网友对JetBrains家的产品好评如潮。甚至有人给出「JetBrains的产品经理就是个经验丰富的程序员」这样高度评价。从侧面也反映出这份报告的含金量。</p>
<p>该报告针对目前市面上的核心编程语言进行分析，其中包括了使用标准，编译器，管理器，开发框架，开发平台和开发目的等信息。</p>
<p>本次报告揭示了一些让人意想不到的事实。</p>
<p>996「榨干」了程序员，一天光围着代码转了</p>
<p>今年JetBrains的报告增加了一个新的版块，开发者的生活方式。受访的开发者会被问及他们的生活习惯、信息搜寻模式和慈善行为等生活问题，有大约三分之二的人公开了他们的生活。</p>
<p>美好的一天从coding开始？33%的受访者表示，他们的编码生活从早上9点开始。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651526.png" alt="img"></p>
<p>开发者有时也想睡懒觉。通常不会关闭早起的闹钟，但有时还是想多睡一会。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651548.png" alt="img"></p>
<p>32%的开发者每周的工作时间超过了32小时（如果国内的受访者样本多一些，想必这个比例会升高不少）。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651596.png" alt="img"></p>
<p>Side Projects？38%的受访者表示，每周可以有3-8个小时的自由时间可以为自己写写代码。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651503.png" alt="img"></p>
<p>Reddit,Stack Overflow当仁不让成为开发者最关注的网站，Reddit上可以了解最新的开发者信息，而遇到棘手的问题，Stack Overflow上有很多热心的开发者能帮你快速解决，并且不用会员、积分之类的，用户体验十分友好。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651471.png" alt="img"></p>
<p>开发者是一群对世界充满爱的家伙，一心希望「让世界更美好」</p>
<p>开发者绝对是一群对这个世界爱的深沉的家伙们！有52%的受访者表示参加过慈善活动，62%的程序员想用代码构建一个更好的世界。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651622.png" alt="img"></p>
<p>而且，开发者是很难将工作、爱好分开的。他们热爱着编程，即使因为工作而coding失去生活中的其他方面。</p>
<p>60%的开发者没有养宠物，但是58%的程序员自称自己的爱好就是编程。看来，代码就相当于他们的宠物了。</p>
<p>而喜欢养小动物的人当中，养猫和养狗的程序员不相伯仲。但如果从猫狗的不同性格推测的话，养猫的程序员可能心思更细腻；养狗的程序员可能身体更健康，因为需要遛狗。</p>
<p>   <img src="http://iubest.gitee.io/pic/640-1601007651540.png" alt="img"></p>
<p>你会相信AI法官的判断吗？只有5%的开发者说yes。也难怪，毕竟开发者最懂他们自己开发的人工智能。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651546.png" alt="img"></p>
<p>受访者20%来自中国，自由职业者仅占5%</p>
<p>63%的受访者为全职，人人都羡慕的Freelancer只占5%，如果你想做个自由职业的开发者，需要付出更多的努力或者接受更多的不确定性。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601366847682.png" alt="img"></p>
<p>程序员逃不过35岁魔咒？就调查结果来看，将近一半的开发者在30岁以下，超过40岁以上的仅有8%，所以大龄程序员们还是要尽早规划。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651676.png" alt="img"></p>
<p>你觉得自己的岗位在公司中有多重要？大部分开发者还是对自己写的代码信心满满，72%的开发者认为自己的岗位处于中高层。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651620.png" alt="img"></p>
<p>这些统计数据都来自哪些国家？有20%的受访者来自中国，美国紧随其后有19%，虽然样本不能代表整个开发者群体，但也可以看出使用JetBrains的用户中，中国、美国、印度还是占了主流。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651621.png" alt="img"></p>
<p>开发语言大比拼：Python弯道超车Java，Go、Kotlin崛起，Typescirpt成最大黑马</p>
<p><strong>Python后劲很猛，Java地位堪忧</strong></p>
<p>Java作为开发语言一哥，已经几十年没被人撼动过了。但报告中却指出，在过去的12个月内，使用Python使用量激增，居然超过了Java！</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651592.png" alt="img"></p>
<p>并且，Python已经成为许多开发语言学习者的首选。</p>
<p>但瘦死的骆驼比马大，Java还是在总量上挽回了一些尊严。（顺便一提的是，作为JetBrains的拳头产品，IntelliJ IDEA的用户满意度高达98%！很难说Java常年保持开发语言一哥的原因跟JetBrains的贡献无关。）</p>
<p><strong>语言迁移首选Go和Kotlin</strong></p>
<p>随着开发需求的日益复杂，许多场景都面临着开发语言迁移的问题。Go，Kotlin和Python是现今最受欢迎的迁移对象，是许多开发者迁移的首选。</p>
<p>Go和Kotlin都是Google在大力推广的新兴开发语言。尤其是Kotlin，大有在Android中取代Java的态势。巧的是，Kotlin刚好也是JetBrains家的产品。</p>
<p>下图很好的表现编程语言的现今使用频率以及未来趋势。总的来说，使用比率前五的语言都势均力敌，JavaScript拔得头筹，使用比率高达70%，HTML/CSS、SQL、Python、Java紧随其后。和去年相比，前五名的编程语言变化不大。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601366866834.png" alt="img"></p>
<p>这其实也很好理解。回想一下Web开发一天要面对多少次的需求更改？一个页面的按钮可能今天放在页面中部、明天放在页面右侧。自然使用频率要远高于Java、C这类更偏向后端、更复杂的语言。</p>
<p>而热门的迁移「对象」：Go，Kotlin和Python可能预示着接下来五年的开发趋势的转变，尤其是Python在人工智能领域做出的巨大贡献，让人相信在未来它会越来越受欢迎。</p>
<p><strong>开发者首选JS，Typescirpt成最大黑马</strong></p>
<p>开发者首选的编程语言还是集中在JavaSript，Java和Python等。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651626.png" alt="img"></p>
<p>和2017年的报告相比，Python确实是势头迅猛。2017年，仅有10%的开发人员将Python作为自己的首选编程语言，当时位列第四，屈居PHP语言之下。今年的报告中，这个数字达到了31%，在短短四年中增长了21%。</p>
<p>值得一提的是，Typescirpt在编程语言表现中颇有「黑马」的劲头。尽管今年的使用率为28%不算很高，也仅有12%的开发者将其作为首选，但对比其2017年12%的使用率和1%的作为首选，Typescirpt也算是进步飞速。</p>
<p><strong>用Windows做开发居多；跨平台框架Flutter增长亮眼</strong></p>
<p>开发环境和操作系统方面，仍旧是由Windows，Linux和macOS在瓜分蛋糕，其余操作系统总占比不到1%。而三者之中，Windows仍以60%的比率稳居第一。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651589.png" alt="img"></p>
<p>Linux在近些年来越来越受到开发者的喜爱，在2017年，仅有40%的开发者选择Linux作为开发平台，而在2020年，有50%开发者选择了Linux，超过了macOS。</p>
<p>和前几年的报告相比，开发类型的受欢迎程度变化不大，后端开发和前端开发依旧是主流，分别占比69%和57%。这个数字基本与几年前的数据都持平。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651548.png" alt="img"></p>
<p>对于移动端开发而言，本地开发工具仍然是移动端开发中最受欢迎的解决方案。三分之二的移动开发人员都在使用它们。此外，一半的移动开发人员使用跨平台技术或框架。近些年的报告显示，React Native始终是最受欢迎的跨平台框架。</p>
<p>​    <img src="http://iubest.gitee.io/pic/640-1601007651715.png" alt="img"></p>
<p>在过去的一年中，Flutter的受欢迎程度有所提高，从30%增长到了39%。Cordova，Ionic和Xamarin则显得没那么流行了，各下降了约10%。</p>
<p>此外，报告还揭示了几个很有意思的点：</p>
<ol>
<li>大多数人都有 GitHub 账号（但<strong>并非全部）</strong></li>
<li>受访的开发者中有一半<strong>不相信人工智能</strong></li>
<li>65% 的人更喜欢笔记本电脑（台式机没落了）</li>
<li>开发人员非常关心私人数据收集</li>
<li>16% 的人使用加密货币</li>
<li>他们在<strong>手机上阅读专业材料</strong>（亟需更好的在小屏幕上阅读代码的解决方案）</li>
</ol>
<p><strong>完整报告请戳</strong>：<a target="_blank" rel="noopener" href="https://www.jetbrains.com/lp/devecosystem-2020/">https://www.jetbrains.com/lp/devecosystem-2020/</a></p>
 
      <!-- reward -->
      
    </div>
    

    <!-- copyright -->
    
    <footer class="article-footer">
       
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/other/" rel="tag">other</a></li></ul>

    </footer>
  </div>

    
 
   
</article>

    
    <article
  id="post-other/为什么HTTPS是安全的"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h2 itemprop="name">
  <a class="article-title" href="/2020/11/11/other/%E4%B8%BA%E4%BB%80%E4%B9%88HTTPS%E6%98%AF%E5%AE%89%E5%85%A8%E7%9A%84/"
    >为什么HTTPS是安全的.md</a> 
</h2>
 

    </header>
     
    <div class="article-meta">
      <a href="/2020/11/11/other/%E4%B8%BA%E4%BB%80%E4%B9%88HTTPS%E6%98%AF%E5%AE%89%E5%85%A8%E7%9A%84/" class="article-date">
  <time datetime="2020-11-10T16:00:00.000Z" itemprop="datePublished">2020-11-11</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/other/">other</a>
  </div>
   
    </div>
      
    <div class="article-entry" itemprop="articleBody">
       
  <h1 id="为什么HTTPS是安全的"><a href="#为什么HTTPS是安全的" class="headerlink" title="为什么HTTPS是安全的"></a>为什么HTTPS是安全的</h1><h3 id="1-HTTP-协议"><a href="#1-HTTP-协议" class="headerlink" title="1. HTTP 协议"></a>1. HTTP 协议</h3><p>在谈论 HTTPS 协议之前，先来回顾一下 HTTP 协议的概念。</p>
<h4 id="1-1-HTTP-协议介绍"><a href="#1-1-HTTP-协议介绍" class="headerlink" title="1.1 HTTP 协议介绍"></a>1.1 HTTP 协议介绍</h4><p>HTTP 协议是一种基于文本的传输协议，它位于 OSI 网络模型中的<code>应用层</code>。</p>
<p><img src="http://iubest.gitee.io/pic/640-1601018039622.webp" alt="img"></p>
<p>HTTP 协议是通过客户端和服务器的请求应答来进行通讯，目前协议由之前的 <strong>RFC 2616</strong> 拆分成立六个单独的协议说明（RFC 7230、RFC 7231、RFC 7232、RFC 7233、RFC 7234、RFC 7235），通讯报文如下：</p>
<ul>
<li>请求</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">POST http:&#x2F;&#x2F;www.baidu.com HTTP&#x2F;1.1</span><br><span class="line">Host: www.baidu.com</span><br><span class="line">Connection: keep-alive</span><br><span class="line">Content-Length: 7</span><br><span class="line">User-Agent: Mozilla&#x2F;5.0 (Windows NT 10.0; Win64; x64) AppleWebKit&#x2F;537.36 (KHTML, like Gecko) Chrome&#x2F;71.0.3578.98 Safari&#x2F;537.36</span><br><span class="line"></span><br><span class="line">wd&#x3D;HTTP</span><br></pre></td></tr></table></figure>

<ul>
<li>响应</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">HTTP&#x2F;1.1 200 OK</span><br><span class="line">Connection: Keep-Alive</span><br><span class="line">Content-Encoding: gzip</span><br><span class="line">Content-Type: text&#x2F;html;charset&#x3D;utf-8</span><br><span class="line">Date: Thu, 14 Feb 2019 07:23:49 GMT</span><br><span class="line">Transfer-Encoding: chunked</span><br><span class="line"></span><br><span class="line">&lt;html&gt;...&lt;&#x2F;html&gt;</span><br></pre></td></tr></table></figure>

<h4 id="1-2-HTTP-中间人攻击"><a href="#1-2-HTTP-中间人攻击" class="headerlink" title="1.2 HTTP 中间人攻击"></a>1.2 HTTP 中间人攻击</h4><p>HTTP 协议使用起来确实非常的方便，但是它存在一个致命的缺点：<code>不安全</code>。</p>
<p>我们知道 HTTP 协议中的报文都是以明文的方式进行传输，不做任何加密，这样会导致什么问题呢？下面来举个例子：</p>
<ol>
<li>小明在 JAVA 贴吧发帖，内容为<code>我爱JAVA</code>：<br><img src="http://iubest.gitee.io/pic/640-1601018039623.webp" alt="img"></li>
<li>被中间人进行攻击，内容修改为<code>我爱PHP</code><br><img src="http://iubest.gitee.io/pic/640-1601018039798.webp" alt="img"></li>
<li>小明被群嘲(手动狗头)</li>
</ol>
<p>可以看到在 HTTP 传输过程中，中间人能看到并且修改 HTTP 通讯中所有的请求和响应内容，所以使用 HTTP 是非常的不安全的。</p>
<h4 id="1-3-防止中间人攻击"><a href="#1-3-防止中间人攻击" class="headerlink" title="1.3 防止中间人攻击"></a>1.3 防止中间人攻击</h4><p>这个时候可能就有人想到了，既然内容是明文那我使用<code>对称加密</code>的方式将报文加密这样中间人不就看不到明文了吗，于是如下改造：</p>
<ol>
<li>双方约定加密方式<br><img src="http://iubest.gitee.io/pic/640-1601018039429.webp" alt="img"></li>
<li>使用 AES 加密报文<br><img src="http://iubest.gitee.io/pic/640-1601167940514.webp" alt="img"></li>
</ol>
<p>这样看似中间人获取不到明文信息了，但其实在通讯过程中还是会以明文的方式暴露加密方式和秘钥，如果第一次通信被拦截到了，那么秘钥就会泄露给中间人，中间人仍然可以解密后续的通信：</p>
<p><img src="http://iubest.gitee.io/pic/640-1601018039392.webp" alt="img"></p>
<p>那么对于这种情况，我们肯定就会考虑能不能将秘钥进行加密不让中间人看到呢？答案是有的，采用<code>非对称加密</code>，我们可以通过 RSA 算法来实现。</p>
<p>在约定加密方式的时候由服务器生成一对<code>公私钥</code>，服务器将<code>公钥</code>返回给客户端，客户端本地生成一串秘钥(<code>AES_KEY</code>)用于<code>对称加密</code>，并通过服务器发送的<code>公钥</code>进行加密得到(<code>AES_KEY_SECRET</code>)，之后返回给服务端，服务端通过<code>私钥</code>将客户端发送的<code>AES_KEY_SECRET</code>进行解密得到<code>AEK_KEY</code>,最后客户端和服务器通过<code>AEK_KEY</code>进行报文的加密通讯，改造如下：</p>
<p><img src="http://iubest.gitee.io/pic/640-1601018039882.webp" alt="img"></p>
<p>可以看到这种情况下中间人是窃取不到用于<code>AES加密</code>的秘钥，所以对于后续的通讯是肯定无法进行解密了，那么这样做就是绝对安全了吗？</p>
<p>所谓道高一尺魔高一丈，中间人为了对应这种加密方法又想出了一个新的破解方案，既然拿不到<code>AES_KEY</code>，那我就把自己模拟成一个客户端和服务器端的结合体，在<code>用户-&gt;中间人</code>的过程中中间人模拟服务器的行为，这样可以拿到用户请求的明文，在<code>中间人-&gt;服务器</code>的过程中中间人模拟客户端行为，这样可以拿到服务器响应的明文，以此来进行中间人攻击：</p>
<p><img src="http://iubest.gitee.io/pic/640-1601018039446.webp" alt="img"></p>
<p>这一次通信再次被中间人截获，中间人自己也伪造了一对公私钥，并将公钥发送给用户以此来窃取客户端生成的<code>AES_KEY</code>，在拿到<code>AES_KEY</code>之后就能轻松的进行解密了。</p>
<p>中间人这样为所欲为，就没有办法制裁下吗，当然有啊，接下来我们看看 HTTPS 是怎么解决通讯安全问题的。</p>
<h3 id="2-HTTPS-协议"><a href="#2-HTTPS-协议" class="headerlink" title="2. HTTPS 协议"></a>2. HTTPS 协议</h3><h4 id="2-1-HTTPS-简介"><a href="#2-1-HTTPS-简介" class="headerlink" title="2.1 HTTPS 简介"></a>2.1 HTTPS 简介</h4><p>HTTPS 其实是<code>SSL+HTTP</code>的简称,当然现在<code>SSL</code>基本已经被<code>TLS</code>取代了，不过接下来我们还是统一以<code>SSL</code>作为简称，<code>SSL</code>协议其实不止是应用在<code>HTTP</code>协议上，还在应用在各种应用层协议上，例如：<code>FTP</code>、<code>WebSocket</code>。</p>
<p>其实<code>SSL</code>协议大致就和上一节<code>非对称加密</code>的性质一样，握手的过程中主要也是为了交换秘钥，然后再通讯过程中使用<code>对称加密</code>进行通讯，大概流程如下：</p>
<p><img src="http://iubest.gitee.io/pic/640-1601018039764.webp" alt="img"></p>
<p>这里我只是画了个示意图，其实真正的 SSL 握手会比这个复杂的多，但是性质还是差不多，而且我们这里需要关注的重点在于 HTTPS 是如何防止中间人攻击的。</p>
<p>通过上图可以观察到，服务器是通过 SSL 证书来传递<code>公钥</code>，客户端会对 SSL 证书进行验证，其中证书认证体系就是确保<code>SSL</code>安全的关键，接下来我们就来讲解下<code>CA 认证体系</code>，看看它是如何防止中间人攻击的。</p>
<h4 id="2-2-CA-认证体系"><a href="#2-2-CA-认证体系" class="headerlink" title="2.2 CA 认证体系"></a>2.2 CA 认证体系</h4><p>上一节我们看到客户端需要对服务器返回的 SSL 证书进行校验，那么客户端是如何校验服务器 SSL 证书的安全性呢。</p>
<ul>
<li><p>权威认证机构</p>
<p>在 CA 认证体系中，所有的证书都是由权威机构来颁发，而权威机构的 CA 证书都是已经在操作系统中内置的，我们把这些证书称之为<code>CA根证书</code>：</p>
<p><img src="http://iubest.gitee.io/pic/640-1601018039676.webp" alt="img"></p>
</li>
<li><p>签发证书</p>
<p>我们的应用服务器如果想要使用 SSL 的话，需要通过权威认证机构来签发<code>CA证书</code>，我们将服务器生成的公钥和站点相关信息发送给<code>CA签发机构</code>，再由<code>CA签发机构</code>通过服务器发送的相关信息用<code>CA签发机构</code>进行加签，由此得到我们应用服务器的证书，证书会对应的生成证书内容的<code>签名</code>，并将该<code>签名</code>使用<code>CA签发机构</code>的私钥进行加密得到<code>证书指纹</code>，并且与上级证书生成关系链。</p>
<p>这里我们把百度的证书下载下来看看：</p>
</li>
</ul>
<p><img src="http://iubest.gitee.io/pic/640-1601018040108.webp" alt="img"></p>
<p>可以看到百度是受信于<code>GlobalSign G2</code>，同样的<code>GlobalSign G2</code>是受信于<code>GlobalSign R1</code>，当客户端(浏览器)做证书校验时，会一级一级的向上做检查，直到最后的<code>根证书</code>，如果没有问题说明<code>服务器证书</code>是可以被信任的。</p>
<ul>
<li><p>如何验证服务器证书</p>
<p>那么客户端(浏览器)又是如何对<code>服务器证书</code>做校验的呢，首先会通过层级关系找到上级证书，通过上级证书里的<code>公钥</code>来对服务器的<code>证书指纹</code>进行解密得到<code>签名(sign1)</code>，再通过签名算法算出服务器证书的<code>签名(sign2)</code>，通过对比<code>sign1</code>和<code>sign2</code>，如果相等就说明证书是没有被<code>篡改</code>也不是<code>伪造</code>的。</p>
</li>
</ul>
<p><img src="http://iubest.gitee.io/pic/640-1601018039535.webp" alt="img"></p>
<p>这里有趣的是，证书校验用的 RSA 是通过私钥加密证书签名，公钥解密来巧妙的验证证书有效性。</p>
<p>这样通过证书的认证体系，我们就可以避免了中间人窃取<code>AES_KEY</code>从而发起拦截和修改 HTTP 通讯的报文。</p>
<h3 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h3><p>首先先通过对 HTTP 中间人攻击的来了解到 HTTP 为什么是不安全的，然后再从安全攻防的技术演变一直到 HTTPS 的原理概括，希望能让大家对 HTTPS 有个更深刻的了解。</p>
 
      <!-- reward -->
      
    </div>
    

    <!-- copyright -->
    
    <footer class="article-footer">
       
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/other/" rel="tag">other</a></li></ul>

    </footer>
  </div>

    
 
   
</article>

    
  </article>
  

  
  <nav class="page-nav">
    
    <a class="extend prev" rel="prev" href="/page/6/">上一页</a><a class="page-number" href="/">1</a><span class="space">&hellip;</span><a class="page-number" href="/page/5/">5</a><a class="page-number" href="/page/6/">6</a><span class="page-number current">7</span><a class="page-number" href="/page/8/">8</a><a class="page-number" href="/page/9/">9</a><span class="space">&hellip;</span><a class="page-number" href="/page/14/">14</a><a class="extend next" rel="next" href="/page/8/">下一页</a>
  </nav>
  
</section>
</div>

      <footer class="footer">
  <div class="outer">
    <ul>
      <li>
        Copyrights &copy;
        2015-2020
        <i class="ri-heart-fill heart_icon"></i> TzWind
      </li>
    </ul>
    <ul>
      <li>
        
        
        
        由 <a href="https://hexo.io" target="_blank">Hexo</a> 强力驱动
        <span class="division">|</span>
        主题 - <a href="https://github.com/Shen-Yu/hexo-theme-ayer" target="_blank">Ayer</a>
        
      </li>
    </ul>
    <ul>
      <li>
        
        
        <span>
  <span><i class="ri-user-3-fill"></i>访问人数:<span id="busuanzi_value_site_uv"></span></s>
  <span class="division">|</span>
  <span><i class="ri-eye-fill"></i>浏览次数:<span id="busuanzi_value_page_pv"></span></span>
</span>
        
      </li>
    </ul>
    <ul>
      
    </ul>
    <ul>
      
    </ul>
    <ul>
      <li>
        <!-- cnzz统计 -->
        
        <script type="text/javascript" src='https://s9.cnzz.com/z_stat.php?id=1278069914&amp;web_id=1278069914'></script>
        
      </li>
    </ul>
  </div>
</footer>
      <div class="float_btns">
        <div class="totop" id="totop">
  <i class="ri-arrow-up-line"></i>
</div>

<div class="todark" id="todark">
  <i class="ri-moon-line"></i>
</div>

      </div>
    </main>
    <aside class="sidebar on">
      <button class="navbar-toggle"></button>
<nav class="navbar">
  
  <div class="logo">
    <a href="/"><img src="/images/ayer-side.svg" alt="Hexo"></a>
  </div>
  
  <ul class="nav nav-main">
    
    <li class="nav-item">
      <a class="nav-item-link" href="/">主页</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/archives">归档</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/categories">分类</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/tags">标签</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" target="_blank" rel="noopener" href="http://www.baidu.com">百度</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/friends">友链</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/2019/about">关于我</a>
    </li>
    
  </ul>
</nav>
<nav class="navbar navbar-bottom">
  <ul class="nav">
    <li class="nav-item">
      
      <a class="nav-item-link nav-item-search"  title="搜索">
        <i class="ri-search-line"></i>
      </a>
      
      
      <a class="nav-item-link" target="_blank" href="/atom.xml" title="RSS Feed">
        <i class="ri-rss-line"></i>
      </a>
      
    </li>
  </ul>
</nav>
<div class="search-form-wrap">
  <div class="local-search local-search-plugin">
  <input type="search" id="local-search-input" class="local-search-input" placeholder="Search...">
  <div id="local-search-result" class="local-search-result"></div>
</div>
</div>
    </aside>
    <script>
      if (window.matchMedia("(max-width: 768px)").matches) {
        document.querySelector('.content').classList.remove('on');
        document.querySelector('.sidebar').classList.remove('on');
      }
    </script>
    <div id="mask"></div>

<!-- #reward -->
<div id="reward">
  <span class="close"><i class="ri-close-line"></i></span>
  <p class="reward-p"><i class="ri-cup-line"></i>请我喝杯咖啡吧~</p>
  <div class="reward-box">
    
    
  </div>
</div>
    
<script src="/js/jquery-2.0.3.min.js"></script>


<script src="/js/lazyload.min.js"></script>

<!-- Tocbot -->

<script src="https://cdn.jsdelivr.net/npm/jquery-modal@0.9.2/jquery.modal.min.js"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jquery-modal@0.9.2/jquery.modal.min.css">
<script src="https://cdn.jsdelivr.net/npm/justifiedGallery@3.7.0/dist/js/jquery.justifiedGallery.min.js"></script>

<script src="/dist/main.js"></script>

<!-- ImageViewer -->

<!-- Root element of PhotoSwipe. Must have class pswp. -->
<div class="pswp" tabindex="-1" role="dialog" aria-hidden="true">

    <!-- Background of PhotoSwipe. 
         It's a separate element as animating opacity is faster than rgba(). -->
    <div class="pswp__bg"></div>

    <!-- Slides wrapper with overflow:hidden. -->
    <div class="pswp__scroll-wrap">

        <!-- Container that holds slides. 
            PhotoSwipe keeps only 3 of them in the DOM to save memory.
            Don't modify these 3 pswp__item elements, data is added later on. -->
        <div class="pswp__container">
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
        </div>

        <!-- Default (PhotoSwipeUI_Default) interface on top of sliding area. Can be changed. -->
        <div class="pswp__ui pswp__ui--hidden">

            <div class="pswp__top-bar">

                <!--  Controls are self-explanatory. Order can be changed. -->

                <div class="pswp__counter"></div>

                <button class="pswp__button pswp__button--close" title="Close (Esc)"></button>

                <button class="pswp__button pswp__button--share" style="display:none" title="Share"></button>

                <button class="pswp__button pswp__button--fs" title="Toggle fullscreen"></button>

                <button class="pswp__button pswp__button--zoom" title="Zoom in/out"></button>

                <!-- Preloader demo http://codepen.io/dimsemenov/pen/yyBWoR -->
                <!-- element will get class pswp__preloader--active when preloader is running -->
                <div class="pswp__preloader">
                    <div class="pswp__preloader__icn">
                        <div class="pswp__preloader__cut">
                            <div class="pswp__preloader__donut"></div>
                        </div>
                    </div>
                </div>
            </div>

            <div class="pswp__share-modal pswp__share-modal--hidden pswp__single-tap">
                <div class="pswp__share-tooltip"></div>
            </div>

            <button class="pswp__button pswp__button--arrow--left" title="Previous (arrow left)">
            </button>

            <button class="pswp__button pswp__button--arrow--right" title="Next (arrow right)">
            </button>

            <div class="pswp__caption">
                <div class="pswp__caption__center"></div>
            </div>

        </div>

    </div>

</div>

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/photoswipe.min.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/default-skin/default-skin.min.css">
<script src="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/photoswipe.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/photoswipe@4.1.3/dist/photoswipe-ui-default.min.js"></script>

<script>
    function viewer_init() {
        let pswpElement = document.querySelectorAll('.pswp')[0];
        let $imgArr = document.querySelectorAll(('.article-entry img:not(.reward-img)'))

        $imgArr.forEach(($em, i) => {
            $em.onclick = () => {
                // slider展开状态
                // todo: 这样不好，后面改成状态
                if (document.querySelector('.left-col.show')) return
                let items = []
                $imgArr.forEach(($em2, i2) => {
                    let img = $em2.getAttribute('data-idx', i2)
                    let src = $em2.getAttribute('data-target') || $em2.getAttribute('src')
                    let title = $em2.getAttribute('alt')
                    // 获得原图尺寸
                    const image = new Image()
                    image.src = src
                    items.push({
                        src: src,
                        w: image.width || $em2.width,
                        h: image.height || $em2.height,
                        title: title
                    })
                })
                var gallery = new PhotoSwipe(pswpElement, PhotoSwipeUI_Default, items, {
                    index: parseInt(i)
                });
                gallery.init()
            }
        })
    }
    viewer_init()
</script>

<!-- MathJax -->

<!-- Katex -->

<!-- busuanzi  -->


<script src="/js/busuanzi-2.3.pure.min.js"></script>


<!-- ClickLove -->

<!-- ClickBoom1 -->

<!-- ClickBoom2 -->

<!-- CodeCopy -->


<link rel="stylesheet" href="/css/clipboard.css">

<script src="https://cdn.jsdelivr.net/npm/clipboard@2/dist/clipboard.min.js"></script>
<script>
  function wait(callback, seconds) {
    var timelag = null;
    timelag = window.setTimeout(callback, seconds);
  }
  !function (e, t, a) {
    var initCopyCode = function(){
      var copyHtml = '';
      copyHtml += '<button class="btn-copy" data-clipboard-snippet="">';
      copyHtml += '<i class="ri-file-copy-2-line"></i><span>COPY</span>';
      copyHtml += '</button>';
      $(".highlight .code pre").before(copyHtml);
      $(".article pre code").before(copyHtml);
      var clipboard = new ClipboardJS('.btn-copy', {
        target: function(trigger) {
          return trigger.nextElementSibling;
        }
      });
      clipboard.on('success', function(e) {
        let $btn = $(e.trigger);
        $btn.addClass('copied');
        let $icon = $($btn.find('i'));
        $icon.removeClass('ri-file-copy-2-line');
        $icon.addClass('ri-checkbox-circle-line');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPIED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('ri-checkbox-circle-line');
          $icon.addClass('ri-file-copy-2-line');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
      clipboard.on('error', function(e) {
        e.clearSelection();
        let $btn = $(e.trigger);
        $btn.addClass('copy-failed');
        let $icon = $($btn.find('i'));
        $icon.removeClass('ri-file-copy-2-line');
        $icon.addClass('ri-time-line');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPY FAILED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('ri-time-line');
          $icon.addClass('ri-file-copy-2-line');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
    }
    initCopyCode();
  }(window, document);
</script>


<!-- CanvasBackground -->


    
  </div>
</body>

</html>